Install PostgreSQL on AlmaLinux 9

Step 1 – Install

dnf install -y postgresql-server postgresql-contrib
postgresql-setup --initdb
systemctl enable --now postgresql

Step 2 – Connect as superuser

sudo -u postgres psql

Step 3 – Create database and user

CREATE USER appuser WITH PASSWORD 'Stro...

Install PostgreSQL on RHEL 9

Step 1 – Install

dnf install -y postgresql-server postgresql-contrib
postgresql-setup --initdb
systemctl enable --now postgresql

Step 2 – Connect as superuser

sudo -u postgres psql

Step 3 – Create database and user

CREATE USER appuser WITH PASSWORD 'StrongPas...

Install PostgreSQL on Ubuntu 24.04

Step 1 – Install

apt update && apt upgrade -y
apt install -y postgresql postgresql-contrib
systemctl enable --now postgresql

Step 2 – Connect as superuser

sudo -u postgres psql

Step 3 – Create database and user

CREATE USER appuser WITH PASSWORD 'StrongP...

Install PostgreSQL on Debian 12

Step 1 – Install

apt update && apt upgrade -y
apt install -y postgresql postgresql-contrib
systemctl enable --now postgresql

Step 2 – Connect as superuser

sudo -u postgres psql

Step 3 – Create database and user

CREATE USER appuser WITH PASSWORD 'StrongPass...

Install PostgreSQL on Gentoo Linux

Step 1 – Install

emerge --ask dev-db/postgresql
emerge --config dev-db/postgresql
rc-update add postgresql-14 default && rc-service postgresql-14 start

Step 2 – Connect as superuser

sudo -u postgres psql

Step 3 – Create database and user

CREATE USER app...

Install PostgreSQL on Arch Linux

Step 1 – Install

pacman -S --noconfirm postgresql
sudo -u postgres initdb --locale=en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data
systemctl enable --now postgresql

Step 2 – Connect as superuser

sudo -u postgres psql

Step 3 – Create database and user

CREATE...

Install PostgreSQL on OpenBSD 7.5

Step 1 – Install

pkg_add postgresql-server
su -l _postgresql -c 'initdb -D /var/postgresql/data -U postgres -A md5 -W'
rcctl enable postgresql && rcctl start postgresql

Step 2 – Connect as superuser

sudo -u postgres psql

Step 3 – Create database and user

Install PostgreSQL on NetBSD 10

Step 1 – Install

pkgin install postgresql16-server postgresql16-client
su -l pgsql -c 'initdb -D /usr/pkg/pgsql/data'
echo 'pgsql=YES' >> /etc/rc.conf && service postgresql start

Step 2 – Connect as superuser

sudo -u postgres psql

Step 3 – Create database an

PostgreSQL Streaming Replication on AlmaLinux 9

Primary: 192.168.1.10   Standby: 192.168.1.11

Primary – /var/lib/pgsql/data/postgresql.conf

wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB
listen_addresses = '*'

/var/lib/pgsql/data/pg_hba.conf:

host replication replicator...

PostgreSQL Streaming Replication on RHEL 9

Primary: 192.168.1.10   Standby: 192.168.1.11

Primary – /var/lib/pgsql/data/postgresql.conf

wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB
listen_addresses = '*'

/var/lib/pgsql/data/pg_hba.conf:

host replication replicator 192....

PostgreSQL Streaming Replication on Ubuntu 24.04

Primary: 192.168.1.10   Standby: 192.168.1.11

Primary – /etc/postgresql/16/main/postgresql.conf

wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB
listen_addresses = '*'

/etc/postgresql/16/main/pg_hba.conf:

host replication r...

PostgreSQL Streaming Replication on Debian 12

Primary: 192.168.1.10   Standby: 192.168.1.11

Primary – /var/lib/postgres/data/postgresql.conf

wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB
listen_addresses = '*'

/var/lib/postgres/data/pg_hba.conf:

host replication replic...

PostgreSQL Streaming Replication on Gentoo Linux

Primary: 192.168.1.10   Standby: 192.168.1.11

Primary – /var/lib/postgres/data/postgresql.conf

wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB
listen_addresses = '*'

/var/lib/postgres/data/pg_hba.conf:

host replication rep...

PostgreSQL Streaming Replication on Arch Linux

Primary: 192.168.1.10   Standby: 192.168.1.11

Primary – /var/lib/postgres/data/postgresql.conf

wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB
listen_addresses = '*'

/var/lib/postgres/data/pg_hba.conf:

host replication repli...

PostgreSQL Streaming Replication on OpenBSD 7.5

Primary: 192.168.1.10   Standby: 192.168.1.11

Primary – /var/lib/postgres/data/postgresql.conf

wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB
listen_addresses = '*'

/var/lib/postgres/data/pg_hba.conf:

host replication repl...

PostgreSQL Streaming Replication on NetBSD 10

Primary: 192.168.1.10   Standby: 192.168.1.11

Primary – /var/lib/postgres/data/postgresql.conf

wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB
listen_addresses = '*'

/var/lib/postgres/data/pg_hba.conf:

host replication replic...

PostgreSQL Backup and Restore on AlmaLinux 9

Logical – pg_dump

# Single database
sudo -u postgres pg_dump -Fc appdb > /backups/appdb_$(date +%F).dump

# All databases
sudo -u postgres pg_dumpall > /backups/all_$(date +%F).sql

Restore

sudo -u postgres pg_restore -d appdb /backups/appdb_2026-0...

PostgreSQL Backup and Restore on RHEL 9

Logical – pg_dump

# Single database
sudo -u postgres pg_dump -Fc appdb > /backups/appdb_$(date +%F).dump

# All databases
sudo -u postgres pg_dumpall > /backups/all_$(date +%F).sql

Restore

sudo -u postgres pg_restore -d appdb /backups/appdb_2026-05-07....

PostgreSQL Backup and Restore on Ubuntu 24.04

Logical – pg_dump

# Single database
sudo -u postgres pg_dump -Fc appdb > /backups/appdb_$(date +%F).dump

# All databases
sudo -u postgres pg_dumpall > /backups/all_$(date +%F).sql

Restore

sudo -u postgres pg_restore -d appdb /backups/appdb_2026-...

PostgreSQL Backup and Restore on Debian 12

Logical – pg_dump

# Single database
sudo -u postgres pg_dump -Fc appdb > /backups/appdb_$(date +%F).dump

# All databases
sudo -u postgres pg_dumpall > /backups/all_$(date +%F).sql

Restore

sudo -u postgres pg_restore -d appdb /backups/appdb_2026-05-...

PostgreSQL Backup and Restore on Gentoo Linux

Logical – pg_dump

# Single database
sudo -u postgres pg_dump -Fc appdb > /backups/appdb_$(date +%F).dump

# All databases
sudo -u postgres pg_dumpall > /backups/all_$(date +%F).sql

Restore

sudo -u postgres pg_restore -d appdb /backups/appdb_2026-...

PostgreSQL Backup and Restore on Arch Linux

Logical – pg_dump

# Single database
sudo -u postgres pg_dump -Fc appdb > /backups/appdb_$(date +%F).dump

# All databases
sudo -u postgres pg_dumpall > /backups/all_$(date +%F).sql

Restore

sudo -u postgres pg_restore -d appdb /backups/appdb_2026-05...

PostgreSQL Backup and Restore on OpenBSD 7.5

Logical – pg_dump

# Single database
sudo -u postgres pg_dump -Fc appdb > /backups/appdb_$(date +%F).dump

# All databases
sudo -u postgres pg_dumpall > /backups/all_$(date +%F).sql

Restore

sudo -u postgres pg_restore -d appdb /backups/appdb_2026-0...

PostgreSQL Backup and Restore on NetBSD 10

Logical – pg_dump

# Single database
sudo -u postgres pg_dump -Fc appdb > /backups/appdb_$(date +%F).dump

# All databases
sudo -u postgres pg_dumpall > /backups/all_$(date +%F).sql

Restore

sudo -u postgres pg_restore -d appdb /backups/appdb_2026-05-...

Tune PostgreSQL Performance on AlmaLinux 9

postgresql.conf

# Memory
shared_buffers          = 2GB       # 25% of RAM
effective_cache_size    = 6GB       # 50-75% of RAM
work_mem                = 64MB      # per-sort/hash
maintenance_work_mem    = 512MB

# Checkpoints
checkpoint_completion_target...

Tune PostgreSQL Performance on RHEL 9

postgresql.conf

# Memory
shared_buffers          = 2GB       # 25% of RAM
effective_cache_size    = 6GB       # 50-75% of RAM
work_mem                = 64MB      # per-sort/hash
maintenance_work_mem    = 512MB

# Checkpoints
checkpoint_completion_target = 0....

Tune PostgreSQL Performance on Ubuntu 24.04

postgresql.conf

# Memory
shared_buffers          = 2GB       # 25% of RAM
effective_cache_size    = 6GB       # 50-75% of RAM
work_mem                = 64MB      # per-sort/hash
maintenance_work_mem    = 512MB

# Checkpoints
checkpoint_completion_targe...

Tune PostgreSQL Performance on Debian 12

postgresql.conf

# Memory
shared_buffers          = 2GB       # 25% of RAM
effective_cache_size    = 6GB       # 50-75% of RAM
work_mem                = 64MB      # per-sort/hash
maintenance_work_mem    = 512MB

# Checkpoints
checkpoint_completion_target =...

Tune PostgreSQL Performance on Gentoo Linux

postgresql.conf

# Memory
shared_buffers          = 2GB       # 25% of RAM
effective_cache_size    = 6GB       # 50-75% of RAM
work_mem                = 64MB      # per-sort/hash
maintenance_work_mem    = 512MB

# Checkpoints
checkpoint_completion_targe...

Tune PostgreSQL Performance on Arch Linux

postgresql.conf

# Memory
shared_buffers          = 2GB       # 25% of RAM
effective_cache_size    = 6GB       # 50-75% of RAM
work_mem                = 64MB      # per-sort/hash
maintenance_work_mem    = 512MB

# Checkpoints
checkpoint_completion_target...

Tune PostgreSQL Performance on OpenBSD 7.5

postgresql.conf

# Memory
shared_buffers          = 2GB       # 25% of RAM
effective_cache_size    = 6GB       # 50-75% of RAM
work_mem                = 64MB      # per-sort/hash
maintenance_work_mem    = 512MB

# Checkpoints
checkpoint_completion_target...

Tune PostgreSQL Performance on NetBSD 10

postgresql.conf

# Memory
shared_buffers          = 2GB       # 25% of RAM
effective_cache_size    = 6GB       # 50-75% of RAM
work_mem                = 64MB      # per-sort/hash
maintenance_work_mem    = 512MB

# Checkpoints
checkpoint_completion_target =...

PostgreSQL User and Role Management on AlmaLinux 9

Create roles

CREATE ROLE alice WITH LOGIN PASSWORD 'AlicePass!';
CREATE ROLE dbadmin WITH SUPERUSER LOGIN PASSWORD 'AdminPass!';
CREATE ROLE readonly;

Grant privileges

GRANT CONNECT ON DATABASE appdb TO alice;
GRANT USAGE ON SCHEMA public TO...

PostgreSQL User and Role Management on RHEL 9

Create roles

CREATE ROLE alice WITH LOGIN PASSWORD 'AlicePass!';
CREATE ROLE dbadmin WITH SUPERUSER LOGIN PASSWORD 'AdminPass!';
CREATE ROLE readonly;

Grant privileges

GRANT CONNECT ON DATABASE appdb TO alice;
GRANT USAGE ON SCHEMA public TO alic...

PostgreSQL User and Role Management on Ubuntu 24.04

Create roles

CREATE ROLE alice WITH LOGIN PASSWORD 'AlicePass!';
CREATE ROLE dbadmin WITH SUPERUSER LOGIN PASSWORD 'AdminPass!';
CREATE ROLE readonly;

Grant privileges

GRANT CONNECT ON DATABASE appdb TO alice;
GRANT USAGE ON SCHEMA public T...

PostgreSQL User and Role Management on Debian 12

Create roles

CREATE ROLE alice WITH LOGIN PASSWORD 'AlicePass!';
CREATE ROLE dbadmin WITH SUPERUSER LOGIN PASSWORD 'AdminPass!';
CREATE ROLE readonly;

Grant privileges

GRANT CONNECT ON DATABASE appdb TO alice;
GRANT USAGE ON SCHEMA public TO a...

PostgreSQL User and Role Management on Gentoo Linux

Create roles

CREATE ROLE alice WITH LOGIN PASSWORD 'AlicePass!';
CREATE ROLE dbadmin WITH SUPERUSER LOGIN PASSWORD 'AdminPass!';
CREATE ROLE readonly;

Grant privileges

GRANT CONNECT ON DATABASE appdb TO alice;
GRANT USAGE ON SCHEMA public T...

PostgreSQL User and Role Management on Arch Linux

Create roles

CREATE ROLE alice WITH LOGIN PASSWORD 'AlicePass!';
CREATE ROLE dbadmin WITH SUPERUSER LOGIN PASSWORD 'AdminPass!';
CREATE ROLE readonly;

Grant privileges

GRANT CONNECT ON DATABASE appdb TO alice;
GRANT USAGE ON SCHEMA public TO...

PostgreSQL User and Role Management on OpenBSD 7.5

Create roles

CREATE ROLE alice WITH LOGIN PASSWORD 'AlicePass!';
CREATE ROLE dbadmin WITH SUPERUSER LOGIN PASSWORD 'AdminPass!';
CREATE ROLE readonly;

Grant privileges

GRANT CONNECT ON DATABASE appdb TO alice;
GRANT USAGE ON SCHEMA public TO...

PostgreSQL User and Role Management on NetBSD 10

Create roles

CREATE ROLE alice WITH LOGIN PASSWORD 'AlicePass!';
CREATE ROLE dbadmin WITH SUPERUSER LOGIN PASSWORD 'AdminPass!';
CREATE ROLE readonly;

Grant privileges

GRANT CONNECT ON DATABASE appdb TO alice;
GRANT USAGE ON SCHEMA public TO a...