deeejay

psql - PSQL Commands

Jan 5th, 2016
420
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. From bash:
  2. su postgres                                         - switch user to postgres user
  3. psql -d db_name                                     - log into a database
  4. SELECT * FROM some_table                            - perform a query
  5.     OR
  6. psql -d db_name -c "SELECT * FROM some_table"       - log into database and perform a query
  7. q to quit                                           - exit out of query
  8. \q                                                  - exit to bash postgres session
  9. \dt                                                 - list table names
  10. exit                                                - exit to original bash user
  11. pg_dump                                             - backup a single database
  12. pg_dumpall                                          - backup all databases
  13.  
  14. Backup and Recovery:
  15. pg_dump -d scifi -f scifi.sql
  16.  
  17. Secure copy the backup to another machine
  18. ===
  19. scp scifi.sql some_user@some_ipaddress:~/scifi.sql
  20.  
  21. Import database from backup
  22. ===
  23. psql scifi < scifi.sql
  24.  
  25. Change owner to new owner
  26. ===
  27. alter table table_name owner to new_owner - repeat for all tables
  28.  
  29. Backup Scripts
  30. Using gzip
  31. ===
  32. pg_dump -d scifi | gzip > scifi_bak.gz
  33.  
  34. Use native backup
  35. ===
  36. pg_dump -Fc scifi > scifi_bak.bk
  37.  
  38. Restore from native backup
  39. ===
  40. pg_restore -d scifi scifi_bak.bk
Advertisement
Add Comment
Please, Sign In to add comment