Frequently used Postgres commands

Some of the common postgres related commands

1. Logging into local Postgres server

   • sudo psql -U <username>
     Eg: sudo psql -U postgres

2. Creating a new database. (Note: following command need to run after #1)

   • create database <db_name>;
     Eg: create database test_db;

3. Dropping a database. (Note: following command need to run after #1)

   • drop database <db_name>;
     Eg: drop database test_db;

4. Take complete backup dump of a database.

   • pg_dump --no-owner -h <host_ip/endpoint> -p <port> -U <username> <db_name> > <file_path>
     Eg: pg_dump --no-owner -h 00.00.00.00 -p 5432 -U postgres uat_db > test_dump.sql

5. Restoring the dump to an existing new database in local.

   • sudo psql -U <username> -d <db_name> < <file_path>
     Eg: sudo psql -U postgres -d <test_db> < test_dump.sql