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/data
Code language: PHP (php)
Starting the PostgreSQL Server
4. Start the PostgreSQL server:
cd ~/opt/pg16
docker compose up -d
docker ps
Code 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 postgres
Code 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.dump
Code 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.json
Code 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:
Pritam Barhate, with an experience of 14+ years in technology, heads Technology Innovation at Mobisoft Infotech. He has a rich experience in design and development. He has been a consultant for a variety of industries and startups. At Mobisoft Infotech, he primarily focuses on technology resources and develops the most advanced solutions.