A personal cheat sheet for psql commands and PostgreSQL client applications.
| Action | Command | Note |
|---|---|---|
| Connect (option 1) | psql -U <username> -d <dbname> |
|
| Connect (option 2) | psql "dbname=stock_master host=localhost user=<username> password=<pword> port=5432" |
|
| List tables (all schemas) | \dt *.* |
1 |
| List tables (specfic schema) | \dt <myschema>.* |
|
| Copy from csv | psql -c "\copy <tblname> FROM '/tmp/the_file.csv' delimiter '|' csv header" |
SO |
| Quit | \q |
| Action | Command | Note |
|---|---|---|
| Backup to file | pg_dump -Fc -h localhost -U postgres -p 5432 stock_master > E:/postgres_backup/stock_master_yyyymmdd.dump |
|
| Drop database | dropdb -h localhost -U postgres -p 5432 -i stock_master_test |
|
| New database | createdb -h localhost -U postgres -p 5432 -T template0 stock_master_test |
|
| Restore from file | pg_restore -h localhost -U postgres -p 5432 -d stock_master_test E:/postgres_backup/stock_master_20230306.dump |
1. This can be executed with SQL
select * from information_schema.tables
where table_schema not in ('information_schema','public','pg_catalog')
order by 1,2,4,3;
select * from pg_tables
where schemaname not in ('information_schema','public','pg_catalog')
order by 1,2,4,3;