Postgres Cheatsheet commands

Tags

pg, postgresql


===== Login to postgresql =====
$ psql -U postgres
$ psql --username=bob --dbname=postgres -W

## To connect the postgres database inside from docker container.
## E.g., psql -h {docker container ip} --username={user} --dbname={db} -W
$ root@8b2ae1d01df2:/# psql -h 172.23.0.2 -p 5432 --username=bob --dbname=postgres -W

$ sudo apt-get install postgresql-client
$ psql -h 172.17.0.2 -p 5432 -U bob -W

## export 
$ pg_dump -h localhost -U postgres -W -d mydb > mydb.sql

## export postgres DB from docker container
$ pg_dump -h 172.17.0.2 -U postgres -W -d mydb > mydb.sql

## import
$ psql -h localhost -U postgres -W -d mydb < mydb.sql

## import to docker based on docker ip
$ psql -h 172.17.0.2 -U postgres -W -d mydb < mydb.sql  

 

NOTE: default DB = postgres

===== Show all databases =====
$ postgres=# \l

===== Create a new database =====
$ postgres=# CREATE DATABASE postgresdb;

===== Drop a database =====
$ postgres=# DROP DATABASE postgresdb;

===== Connect to database =====
$ postgres=# \connect {DATABASENAME} or using the shortcut-way \c

===== Show all tables =====
$ postgres=# \dt

===== Describe a table =====
$ postgres=# \d {TABLENAME}

===== Select a table =====
$ postgres=# SELECT * FROM {TABLENAME};

$ postgres-# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO bob;