Using PgSQL Command Line Utilities: psql, pg_dump, and pg_restore Tutorial

Introduction

PostgreSQL, also known as Postgres, is a powerful, open-source relational database management system that emphasizes extensibility and SQL compliance. For developers and database administrators, efficiently managing PostgreSQL from the command line is a crucial skill. This tutorial will guide you through common PostgreSQL command line utilities using the command line on Ubuntu 22.04, leveraging Docker to facilitate running multiple PostgreSQL versions simultaneously. By the end of this tutorial, you will be comfortable with starting a PostgreSQL instance, connecting to it, performing basic database operations, and handling backups and restores.

If you prefer video version of this tutorial, it’s available here:

Start a New Instance of PostgreSQL and PostGIS

To begin, you need to start a PostgreSQL instance using Docker. Docker simplifies the process of running different PostgreSQL versions on your machine.

Setting Up Docker Compose for PostgreSQL

1. Go to the PostGIS Docker Hub page and find the image for the desired PostgreSQL version, e.g., postgis/postgis:16-3.4.

2. Create necessary directories and set up the Docker Compose file:

mkdir -p ~/opt/pg16
mkdir -p ~/opt/pg16/data
cd ~/opt/pg16
vim docker-compose.yml Code language: JavaScript (javascript)

3. Add the following content to docker-compose.yml:

version: "3.1"
services:
  db:
    image: postgis/postgis:16-3.4
    container_name: pg-16-postgis
    environment:
      POSTGRES_PASSWORD: mobisoft#8976
    ports:
      - "5416:5432"
    volumes:
      - ${PWD}/data:/var/lib/postgresql/dataCode language: PHP (php)

Starting the PostgreSQL Server

4. Start the PostgreSQL server:

cd ~/opt/pg16
docker compose up -d
docker psCode language: JavaScript (javascript)

This will start the PostgreSQL server on localhost:5416.

Install Only PostgreSQL Client

To interact with PostgreSQL, you need to install the PostgreSQL client:

sudo apt update
sudo apt install -y curl gpg gnupg2 software-properties-common apt-transport-https lsb-release ca-certificates
sudo curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y postgresql-client-16
psql --version
Code language: PHP (php)

This installs the PostgreSQL client, allowing you to connect to the database server.

Connect to PostgreSQL Using psql

The psql command-line tool is used to connect to a PostgreSQL server:

psql -h <hostname> -p <port> -U <db-username> -W <db-name>

psql -h localhost -p 5416 -U postgres -W postgresCode language: HTML, XML (xml)

Replace <hostname>, <port>, <db-username>, and <db-name> with your server’s details.

Basic Database Operations

Create a New Database

To create a new database:

CREATE DATABASE exampledb
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;Code language: JavaScript (javascript)

• LC_COLLATE specifies the collation order.
• LC_CTYPE specifies the character classification.
• TEMPLATE template0 ensures a clean database without preloaded data.

Show Databases

To list all databases:

\l

Toggle Expanded Display Mode

To toggle expanded display mode in psql, use:

\x

Change Database

To switch to another database:

\c exampledb

Create Sample Data with pgbench

pgbench is a benchmarking tool for PostgreSQL. To create sample data:

sudo apt-get install postgresql-contrib-16
pgbench -h localhost -p 5416 -U postgres -i -s 10 exampledb
pgbench -h localhost -p 5416 -U postgres -c 10 -j 2 -T 60 exampledb
Code language: JavaScript (javascript)

Find Table Sizes

Connect to exampledb using psql:

psql -h localhost -p 5416 -U postgres -W exampledb

To find the sizes of tables:

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS total_size
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
    AND
    table_schema = 'public'
ORDER BY
    pg_total_relation_size(table_schema || '.' || table_name) DESC;
Code language: PHP (php)

Adjust the table_schema in the WHERE clause if your tables are not in the public schema.

Run a Query and Save Output to CSV

To run a query and save its output to a CSV file:

mkdir -p ~/backup
psql -h localhost -p 5416 -U postgres -W exampledb -c  "COPY (SELECT * FROM pgbench_accounts) TO STDOUT WITH CSV HEADER" > ~/backup/pgbench_accounts.csv
head -10 ~/backup/pgbench_accounts.csv
cd ~/backup
tar -czvf pgbench_accounts.tar.gz pgbench_accounts.csv
Code language: PHP (php)

You can transfer the backup file using scp:

scp -i file.pem ubuntu@<your_server_ip>:/home/ubuntu/backup/pgbench_accounts.tar.gz ~
Code language: HTML, XML (xml)

Dump Entire Database

To dump the entire database:

cd ~/backup
pg_dump -h localhost -p 5416 -U postgres -W --format=c --file=exampledb.dump exampledb

The –format=c option specifies the custom format, which is already compressed.

Exclude Some Tables from Dump

To exclude specific tables from the dump:

pg_dump -h localhost -p 5416 -U postgres -W --format=c --exclude-table=pgbench_history --file=exampledb_without_history.dump exampledb

Restore Backup to Another Database

To restore a database from a dump:

psql -h localhost -p 5416 -U postgres -W postgres -c  "CREATE DATABASE exampledb_frm_backup LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE template0;"
pg_restore -h localhost -p 5416 -d exampledb_frm_backup -U postgres -W -F c exampledb.dumpCode language: JavaScript (javascript)

Check Query Plans

To check query plans, use the EXPLAIN command. Here is an example:

SELECT
    h.bid AS branch_id,
    b.bbalance AS branch_balance,
    h.aid AS account_id,
    a.abalance AS account_balance,
    h.tid AS teller_id,
    t.tbalance AS teller_balance,
    h.delta AS transaction_amount,
    h.mtime AS transaction_time
FROM
    pgbench_history h
JOIN
    pgbench_accounts a ON h.aid = a.aid
JOIN
    pgbench_branches b ON h.bid = b.bid
JOIN
    pgbench_tellers t ON h.tid = t.tid
ORDER BY
    h.mtime DESC
LIMIT 10;Code language: PHP (php)

Minify the query and get the plan:

psql -h localhost -p 5416 -U postgres -W -d exampledb -qAt -c "EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT h.bid AS branch_id, b.bbalance AS branch_balance, h.aid AS account_id, a.abalance AS account_balance, h.tid AS teller_id, t.tbalance AS teller_balance, h.delta AS transaction_amount, h.mtime AS transaction_time FROM pgbench_history h JOIN pgbench_accounts a ON h.aid = a.aid JOIN pgbench_branches b ON h.bid = b.bid JOIN pgbench_tellers t ON h.tid = t.tid ORDER BY h.mtime DESC LIMIT 10;" > query_plan.json

cat query_plan.jsonCode language: JavaScript (javascript)

Visualize the query plan at PEV.

Conclusion

In this tutorial, you have learned how to handle common PostgreSQL tasks from the command line, including starting a PostgreSQL instance with Docker, connecting to the database, creating and managing databases, handling backups, and examining query plans. Mastering these PostgreSQL command line utilities will enhance your efficiency in managing PostgreSQL databases, making it easier to develop, test, and deploy your applications. With practice, these tasks will become second nature, helping you to better leverage the power and flexibility of PostgreSQL in your projects.

For more insights on PostgreSQL in different environments, you can explore our other tutorials:

• How to Install PostgreSQL and PostGIS in Google Kubernetes Engine GKE.
• Important Gotchas and Limitations About Azure Database for PostgreSQL.

Author's Bio: