PostgreSQL Setup and Usage Guide¶

# Import the repository signing key
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# Create the repository configuration file
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Update the package lists
sudo apt update

# Install the latest version of PostgreSQL and its additional contributed packages

# If you want a specific version, use 'postgresql-16' or similar instead of 'postgresql'
sudo apt -y install postgresql postgresql-contrib

# Start the PostgreSQL service
sudo systemctl start postgresql

# Enable the PostgreSQL service to start automatically on boot
sudo systemctl enable postgresql

# Switch to the default PostgreSQL user 'postgres' to perform administrative tasks
sudo -i -u postgres

# Access the PostgreSQL shell using the psql command to perform database operations
psql

-- Create a new user named 'root' with the password 'root'
CREATE USER root WITH PASSWORD 'root';

-- Create a new database named 'my_database'
CREATE DATABASE my_database;

-- Grant all privileges on 'my_database' to the 'root' user
GRANT ALL PRIVILEGES ON DATABASE my_database TO root;

-- Switch to the newly created database for further configuration
\c my_database 

-- Grant all privileges on the public schema to the 'root' user
GRANT ALL PRIVILEGES ON SCHEMA public TO root;

-- Allow the 'root' user to create objects in 'my_database'
GRANT CREATE ON DATABASE my_database TO root;

# Exit the PostgreSQL shell after completing database configuration
\q
# Exit the PostgreSQL administrative user session
exit

docker run -d \
    --name some-postgres \
  -e POSTGRES_USER=postgres \                 # optional # Default = postgres
    -e POSTGRES_PASSWORD=mysecretpassword \     # mandatory
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    -v /custom/mount:/var/lib/postgresql/data \
    postgres

# Use postgres/example user/password credentials
version: '3.9'

services:

  db:
    image: postgres
    restart: always
    # set shared memory limit when using docker-compose
    shm_size: 128mb
    # or set shared memory limit when deploy via swarm stack
    #volumes:
    #  - type: tmpfs
    #    target: /dev/shm
    #    tmpfs:
    #      size: 134217728 # 128*2^20 bytes = 128Mb
    environment:
      POSTGRES_PASSWORD: example

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080