Postgresql Basic Commands

• Postgres Basic Setup Commands
  • DB dumps
    • Create Database Dumps
      • Create SQL format dump from a local/remote Postgres database
      • Create a text format dump from a local/remote postgres database
      • Create a pgsql format dump
    • Restoring Database dumps
      • Restore DUMP to a RDS Postgres Database
      • Restore a text format dump to database present on localhost
      • Restore a DB dump.gz file

Table of contents generated with markdown-toc

Postgres Basic Setup Commands

Postgres Help (and default Configuration)

• This will list all oprions for psql

psql --help

• For default username check the Connection options output section of the psql --help command

• start postgres service

sudo service postgresql restart

mac os

Installation

brew install postgresql

Complete uninstall

check installation path of postgres with which postgres

brew uninstall postgresql
rm -fr <put the installation path here>

brew services list

restart postgres service

brew services restart postgresql

manual start for mac os

pg_ctl -D /usr/local/var/postgres start

automatic start for mac os

brew services start postgresql

• stop postgres service for mac os

pg_ctl -D /usr/local/var/postgres stop

• login as default user

sudo -u postgres psql postgres

MacOS

• login as default user(i.e postgres)

psql postgres

• check postgres logs for version > 9.6

/usr/local/var/log/postgres.log

• login as specific user

sudo -u username psql postgres
or
sudo -u username psql your_dbname

• check the current user and db connection details In the postgres cli, run

\conninfo

• create user From Terminal

sudo -u postgres createuser username_here;

From psql console

CREATE USER username WITH PASSWORD "password";

• change password

ALTER USER username_here WITH PASSWORD 'password';

• change role to superuser

ALTER USER username_here WITH superuser;

User/Role management

List all users with roles

\du

Create User with password

CREATE USER username WITH PASSWORD 'password';

Create Role

CREATE ROLE rolename;

Add role to user

ALTER USER username WITH rolename;

Database

See all commands for psql console

In the postgres console(psql), run

\?

Create Database

Check the logged in user and connection details with

\conninfo

the database will be created under the current user.

To create database,

CREATE DATABASE database_name;

• see the list of databases In postgres console(psql)

\l

• connect to a database In postgres console(psql)

\c database_name

• list tables of the public schema In postgres console(psql)

\dt

• list tables of certain schema, e.g., \dt public.* In postgres console(psql)

\dt <schema-name>.*

• list tables of all schemas In postgres console(psql)

\dt *.*

DB dumps

Create Database Dumps

Create SQL format dump from a local/remote Postgres database

pg_dump -h <endpoint> -U postgres dbname=your_db_name -f file.sql

Create a Psql format dump from local/remote postgres database

pg_dump -h <endpoint> -U <username> -d <database name> > file.psql

Create a text format dump from a local/remote postgres database

pg_dump -h <endpoint of instance> -U <master username> -d <database name> > file.dump

Create a pgsql format dump

pg_dump -U username dbname > dumpfilename.pgsql

Create a Dump file with no-owner

pg_dump --format=c --compress=9 --no-acl --no-owner -h HOST_HERE -U postgres DB_NAME_HERE > filename.dump

• This dump format will be restored using pg_restore The restore command is

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U USERNAME_HERE -d DB_NAME_HERE dump_file.dump

Restoring Database dumps

Restore to local or AWS

Restore dump to a RDS Postgres Database

pg_restore --verbose --clean --no-acl --no-owner -h <RDS Database Endpoint> -U <Postgres DB Username> -d <Postgres DB Name> /path/to/file.dump

Restore a text format dump to database present on localhost

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U <Postgres DB Username> -d db_name /path/to/file.dump

Restore an sql format file dump

psql -f file.sql --host <endpoint> --port 5432 --username <database username> --dbname <database name>

Psql command can restore custom format backups

If you try to restore a dumpfile with pg_restore but it says that its a custom dump format and use psql to restore it then do the following:

Restore an sql format file dump

psql -f file.extension --host <endpoint> --port 5432 --username <database username> --dbname <database name>

Restore a psql format file dump

pg_restore --verbose --clean --no-acl --no-owner -h <endpoint> -U <database username> -d <database name> file.psql

Restore with psql

psql -U postgres -p 5432 -h alisuq-production-database.c2m9bh3bioit.me-south-1.rds.amazonaws.com -d alisuq_production_db < file.dump

Restore a DB dump.gz file

gunzip -c filename.gz | psql dbname

Restore to Heroku

Restore SQL dump to heroku

heroku pg:psql -a app_name < file.sql