1. psql #databsename#
To connect to named database
2. c #databasename#
To switch DB (think c ==> change)
3. p
To view the recent query buffer
4. analyse verbose #schema.table#
To view the “dead rows”, which need to be vacuumed for maintenance. PostgreSQL does not remove deleted rows from disk by default, you need to vacuum them out.
5. vacuum #schema.table#
Cleans up dead rows etc.
6. e
To switch the currently configured text editor. When you are done, it will run the query in the command line. How EFFING KEWL is that? Configure Sublime and go.
7. i #file name#
To load and execute a sql file.
8. !
interacts with the shell, so you can do:
! mkdir ~/psql/reports
to create a reports directory
9. copy #schema.table# TO #directory name# csv
Uses copy command to copy data (in this case an export) to a file on disk in CSV format
10. copy #schema.table# FROM #directory name# csv
Uses copy command to copy data (in this case an import) from a file on disk in CSV format
11. d #object name#
Describe object == sp_help
12. dt
List all tables.
13. dv
List all views.
14. dtv
List tables and views … etc.
15. explain select …
Display query plan
16. pg_dump.exe [–table #tablename#] -Fc “#database#” > “#filename#”
Dumps a compressed backup file
-Fc ==> think file, compressed
17. pg_restore.exe -d “#database#” -Fc “#filename#”
Restores a compressed backup
-d ==> think destination
-Fc ==> think file, compressed
18. pg_dump.exe [–table #tablename#] “#database#” > “#filename#”
Dumps a plaintext sql backup file
19. psql -d “#database” -f “#backupfile#”
To replay plaintext restore
-d ==> think destination
-f ==> think file
20. createdb.exe “#databasename”
… Creates a database …
21. pg_dump.exe -h localhost “#databasename” | psql.exe -h #other host ip address#
Will do a dump and pipe the result into psql on another host.
We could to the same with the compressed backup here and pipe to pg_restore instead.
22. Sample Powershell backup script:
$date = (Get-Date).tostring(“yyyyMMdd”)
$filepath = “D:Backups”
$filename = “#database to backup#” + $date + “.bak”
$fullpath = $filepath + $filename
#path to postgres bin dir#pg_dump.exe -U postgres “#database to backup” > $fullpath