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

   Alternate commands for the same purpose using DB Url
   - pg_dump -Fp -O $DB_URL > <file_path>
   - -Fp: F stands for format and p means plain sql
   - -O: This indicates not to output owner information.
   Eg: pg_dump -Fp -O $DB_URL > uat_db.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

   Alternate commands for the same purpose using DB Url
   - psql -f <file_path> $DB_URL
   - -f: Tells to execute commands in the file on the DB connected via DB_URL
   Eg: psql -f uat_db.sql $DB_URL