Postgres Cheat Sheet

1. Postgres version
$ psql --version

2. Connect to server locally using default postgres acct
$ psql -U postgres

3. Connect with a password
$ psql -U postgres -W

4. Common commands
\h - help
\q - quit
\l - list databases
\d - list tables
\d table - describe table

5. Create db
postgres=# CREATE DATABASE testdb;

6. Use the db
postgres=# \c testdb;
testdb=#

7. List of users
postgres=# \du

8. List postgres versions running
$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.1 main    5433 down   postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
9.4 main    5432 down   postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
9.6 main    5434 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

9. Start/Stop/Restart/Status of postgresql
$ sudo systemctl start postgresql
$ sudo systemctl stop postgresql
$ sudo systemctl restart postgresql
$ sudo systemctl status postgresql

10. Backup/Restore
$ pg_dump -Fc -h hostname -p 5432 -d dbname -f /path/backupfilename
$ pg_restore -d dbname -v -1 /path/backupfilename

11. Cron daily backup
$ crontab -e
0 5 * * * export PGPASSWORD=MyPass && pg_dump -Fc -h hostname -p 5432 -d dbname -f /path/backupfilename_`date +20\%y\%m\%d` >> /path/pg_dump_dbname.log 2>&1