PostgreSQL Cheatsheet
By Sudheer Satyanarayana
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
Allowing Password Based Login From localhost
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
Allowing Password Based Login From A Particular Network
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
