Postgres Cheat Sheet

$ sudo -u postgres psql

# switch user
$ sudo su - postgres

# connect to postgresql
$ psql

\q

\l

# include details like description, tablespace & DB size
\l+

\c database_name

create database database_name

drop database database_name

\dt

\d table_name

# include more details
\d+ table_name

\du

select current_user;

\conninfo

alter database current_db_name rename to new_db_name;

grant all privileges on database db_name to user_name;

\dt name_of_schema.*

Comment on table user is 'App tenant records';

Comment on column user.pin is 'Tenant tax identification Number';

\copy (SELECT * FROM table_name) TO 'file_path_and_name.csv' WITH CSV

psql mydb -f file.sql

psql -U user_here -h host_ip_here -p port_here -d db_name_here

# example
psql -U admin -h 35.21.09.12 -p 2506 -d mydb

psql -c "\l+" -H postgres > database.html

psql -V

psql -W db_name

psql -h localhost -p 5432 db_name

psql -U user_name database_name

pg_dumpall -U postgres > all.sql

pg_dump -d db_name -f mydb_backup.sql

# Important flags

-a   Dump only the data, not the schema
-s   Dump only the schema, no data
-c   Drop database before recreating
-C   Create database before restoring
-t   Dump the named table(s) only
-F   Format (c: custom, d: directory, t: tar)

psql -U user db_name < mydb_backup.sql

# alternative

pg_restore -d db_name mydb_backup.sql -c

# Important flags

-U   Specify a database user
-c   Drop database before recreating
-C   Create database before restoring
-e   Exit if an error has encountered
-F   Format (c: custom, d: directory, t: tar, p: plain text sql(default))

\copy table FROM '<path>' CSV
\copy table(col1,col1) FROM '<path>' CSV

\dp

# a).

# 1. Login into the psql:

$ sudo -u postgres psql


# 2. Then in the psql console change the password and quit:

postgres=# \password postgres
Enter new password: <new-password>
postgres=# \q

# or 

# b). 

# 1. To log in without a password:

sudo -u user_name psql db_name

# 2. To reset the password if you have forgotten:

ALTER USER user_name WITH PASSWORD 'new_password';