- Why you may want to read this article
- Prerequisites
- Use case: Setting up using docker run
- Use case: Setting up using docker-compose
- Commands
Why you may want to read this article
Disclaimer: This article DOES NOT contain any rocket science information, or anything extraordinary, it is created for convenience only to have everything on hand.
During my last 5 years of experience, there were a lot of projects that used PostgreSQL as the main database.
For sure, to develop such projects - you need to set up a database locally all the time. In my opinion docker - is the most convenient and quick way to set up external services for local development.
On top of that, I had experience setting up Postgres for production in an on-prem environment.
So this article is about a bunch of commands that you may find useful. I decided to collect them all together in one place to not googling them all the time.
Prerequisites
Docker
For sure you could use direct installation by exe (Windows) or command line. There are different drawbacks.
I prefer docker because you :
- keep your app isolated
- keep your app more controllable
- you can easily turn it off/on
- you easily control all configs including ports, user/password
- you can set up multiple instances of Postgres on different ports
- …
You should have an understanding of basic things in docker. It is better to learn them outside, but I will leave definitions here for convenience.
PgAdmin (optional)
This is a pretty convenient UI application for the Postgres database. But, as a true SE, we are too lazy to install it separately - so we will use the command line.
However if you need perform a lot of operations and have good visualization of your data, here is docker command to do it:
docker run -p 5555:80 --name pgadmin -e PGADMIN_DEFAULT_EMAIL=root@gmail.com -e PGADMIN_DEFAULT_PASSWORD=root -d dpage/pgadmin4
This command will start latest pgadmin instance on port 5555. To open it - go to any browser, navigate to http://localhost:5555 or straight away to login page http://localhost:5555/login?next=%2F. Fill in email address with root@gmail.com and password with root and you will be logged to fancy PgAdmin UI.
Terminology
Image - set of instructions about how to make your container
Container - is running or not running an application inside the docker
Volume - is a mechanism to persist data out of the conteiner’s life. So if the container is dropped the data stays persisted by volume. The mechanism is usually syncing data from the container in the host operating system.
Use case: Setting up using docker run
Simple use case if you have raw docker, you could leverage the command line.
First, create volume:
docker volume create pgdata
Then let’s create and run a postgres container with the name “postgres”, on default port 5432, with “root” login and “root” password, and map “pgdata” volume to Postgres’ data.
docker run -p 5432:5432 --name postgres -v pgdata:/var/lib/postgresql/data -e POSTGRES_PASSWORD=root -e POSTGRES_USER=root -d postgres:14
Use case: Setting up using docker-compose
Just create docker-compose.yml file somewhere and put this into it:
version: "3.7"
services:
db:
image: postgres
container_name: postgres
ports:
- 5432:5432
volumes:
- db-data:/var/lib/postgresql/data
environment:
POSTGRES_USER: root
POSTGRES_PASSWORD: root
volumes:
db-data:
Then go to the folder that contains docker-compose.yml and run from it using cmd:
docker-compose up -d
With this command, we did pretty much the same as using just run command: mapped volume to Postgres’ data, created a container with root login and root password, and default port.
Commands
Go inside the container:
docker exec -it postgres bash
Connect to database:
psql -h 127.0.0.1 -p 5432 -U root
To list all databases:
\l
Create database:
#option 1
psql -h 127.0.0.1 -p 5432 -U root
CREATE DATABASE "Test";
#option 2
psql -h 127.0.0.1 -p 5432 -U root -c 'CREATE DATABASE "Test2"'
Drop database:
#option 1
psql -h 127.0.0.1 -p 5432 -U root
DROP DATABASE "Test";
#option 2
psql -h 127.0.0.1 -p 5432 -U root -c 'DROP DATABASE "Test2"'
Connect to database:
\c <DBNAME>
List tables in the connected database:
\dt
To make a backup (the file will be created on host machine):
docker exec postgres pg_dump Test > dump.sql
To make a backup of single table in SQL statements (instead of COPY) statements
docker exec postgres pg_dump --column-inserts --data-only --table={table-name} PetForPet > dump.sql
Restore database from backup (you first should create db using commands above):
docker cp ./dump.sql postgres:/dump.sql
docker exec -it postgres bash
psql -h 127.0.0.1 -p 5432 -U root -c 'CREATE DATABASE "Test2"'
psql -h 127.0.0.1 -U root -d Test2 < dump.sql