PostgreSQL Cheatsheet

By Sudheer S

July 25, 2016

Install PostgreSQL Server

Fedora and CentOS:

sudo dnf install postgresql-server

Ubuntu 18.04:

sudo apt install postgresql

New Server Initialization

On CentOS 7/Fedora 30:

sudo postgresql-setup initdb

Upgrading From An Older Version

sudo postgresql-setup --upgrade

Administering The Database Server

Managing The `postgresql` Daemon

Starting PostgreSQL server

sudo systemctl start postgresql

Checking PostgreSQL Server Status:

sudo systemctl status postgresql

Enabling PostgreSQL Server Systemd Unit/Enabling PostgreSQL Server On Boot:

sudo systemctl enable postgresql

Edit /var/lib/pgsql/data/pg_hba.conf as privileged user(root) and add this line:

host    all     all     127.0.0.1/32    md5

Edit /var/lib/pgsql/data/pg_hba.conf as privileged user(root) and add this line:

host    all             all             192.168.1.0/24          md5

In this example password based login will be allowed from the network 192.168.1.0/24.

After making changes to the pg_hba.conf file, restart PostgreSQL server.

Restarting The PostgreSQL server:

sudo systemctl restart postgresql

Listening On A Particular IP Address:

Edit the file /var/lib/pgsql/data/postgresql.conf and add:

listen_addresses = '192.168.1.5'

Save the file and restart PostgreSQL server. This configuration will allow PostgreSQL to listen on the IP address 192.168.1.5.

Listening On All IP addresses:

Edit the file /var/lib/pgsql/data/postgresql.conf and add:

listen_addresses = '*'

Save the file and restart PostgreSQL server.

Becoming `postgres` :

The root user of postgresql is postgres.

sudo su - postgres

Launching The PostgreSQL Console:

psql

From the PostgreSQL console, you can perform administrative tasks such as creating and deleting users, databases, etc.

Creating A PostgreSQL User

 CREATE USER username login PASSWORD 'password';

Creating A PostgreSQL Database

 CREATE DATABASE databasename WITH OWNER = username;

Listing Databases

Launch the PostgreSQL console:

sudo su - postgres
psql

From the PostgreSQL console, execute \d

\d

Listing Tables Of A Database

From the PostgreSQL console:

\d DATABASE_NAME

Replace DATABASE_NAME with the actual database name.

Backing Up A Database:

pg_dump -UUSERNAME -h POSTGRESQL_SERVER_HOSTNAME DATABASE_NAME > BACKUP_FILE_NAME

For example:

pg_dump -Uusername -h 127.0.0.1 databasename > my-pg-databasename-backup.sql

Restoring A Database From A Dump File

Copy the file to the host where the dump has to be restored.

sudo su - postgres
psql databasename < /path/to/dump/file

Generating The CREATE TABLE Statement For A Particular Table

pg_dump -U username -h 127.0.0.1 dbname -t tablename --schema-only