I am posting some of the PostgreSQL commands which I use frequently.
Create a new database name testdb
# createdb <dbname>
# createdb testdb
Remove a PostgreSQL database
# dropdb <dbname>
# dropdb testdb
Backing up a PostgreSQL database
# su - postgres
# pg_dump --blob -Fc testdb -f /var/lib/pgsql/backups/testdb_backup
Restoring PostgreSQL database from back up dump
# pg_restore --dbname=testdb /var/lib/pgsql/backups/testdb_backup
Writing query output to a CSV file:
# \o 'tmp/logs/query_out_dump.csv'
After this operation all the query results will be stored in a CSV file.
Using console again for query output:
For more on pg_dump
pl. check the documentation
Along with my AI class course, I have also started DB class (Oct-Dec session) from Standford online courses series. Actually, learning DB was long due and this seems to be perfect opportunity. In my professional life I write SQL queries and sometime modify the database schema but that is all job requirement, going through full course content again (after my degree) is really an opportunity and I don’t want to miss it. Course instructor for this class is Jennifer Widom and I am really liking her teaching style so I am bit excited. Well, I have almost completed first part and some of the points covered in this part are:
- DBMS features
- Key concepts like Data model, schema, DDL, DML
- Key people like Database designer, Administrator, developer
- Relational model XML data – DTD, XSD — This covered really well along with XML validation
- Relational algebra
- Intro to SQL
Have to finish some assignment by this week end. It’s going to be packed weekend with lot of assignments.
Will come back with more..