Back

Postgres


Connect

psql -h server.fqdn -p 5432 -U postgres


Commands:

\du             List users
\l              List databases
\c <dbname>     Connect to <dbname>
\dn             List schemas
\dp             List tables

Typical Queries

SELECT * FROM <table_name>;
SELECT COUNT(*) FROM <table_name>;

Show DB Size

select pg_size_pretty( pg_database_size('<dbname>')) As human_size;

Dump Database

pg_dump -f <dump_file>.dmp -Fc -Z 9 <dbname>

Restore Dump To New DB

createdb -O <owner> <new_dbname>
pg_restore -d <new_dbname> -Fc -j 8 <dump_file>.dmp