PostgreSQL tips

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