PostgreSQL Commands - Database Management & Docker - databases Cheatsheets

This section provides essential PostgreSQL commands for efficient database management and administration. Whether you need to check connectivity, perform backups and restores, manage databases, or set up a PostgreSQL instance with Docker, these commands will streamline your workflow.

PostgreSQL Connectivity and Status

Ensure your PostgreSQL server is accessible and responsive with the pg_isready command.

# check connection to a database on host `localhost` and port `5432`
pg_isready -h localhost -p 5432

Database Backup and Restore

Safeguard your data with robust backup and restore procedures using pg_dump and psql.

# backup database named `anitya`, add CREATE statement
sudo -u postgres pg_dump -C anitya > anitya.dump

# restore database
sudo -u postgres psql -f anitya.dump

Database Creation and Deletion

Manage your databases by creating new ones or removing existing ones with simple psql commands.

# create a database
sudo -u postgres psql -h localhost -p 5432 -U postgres -c "CREATE DATABASE kaizen;"

# delete a database
sudo -u postgres psql -h localhost -p 5432 -U postgres -c "DROP DATABASE kaizen;"

Running PostgreSQL with Docker

Easily deploy and manage PostgreSQL instances using Docker for development and testing environments. This command sets up a container with persistent storage.

# Run a postgres docker container with persistent volume
docker pull postgres
mkdir -p ~/docker/volumes/postgres
docker run --rm  --name pg-docker -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data postgres

These commands are fundamental for any developer or administrator working with PostgreSQL. For more advanced operations and configurations, refer to the official PostgreSQL documentation.