Installation of PostgreSQL

/ home / computer / postgresql / postgresql-for-mysql-admins / .

Which O/S?

Which (Linux) distribution?

Different Branches and Forks of PostgreSQL (Licensen BSD)

• “Vanilla” PostgreSQL
• EnterpriseDB (EDB) (Enterprise PostgreSQL, Encryption at Rest?, Oracle style PL/SQL?)
• Citus (Analytics, Column Store?, Sharding)
• Percona Distribution for PostgreSQL
• CloudNativePG
• Neon
• OrioleDB
• Amazon Aurora
• AlloyDB
• Lakebase/Neon
• Supabase
• Heroku
• Crunchy Data
• TigerData

Which version to use?

Installation methods?

• Distro Repo: apt/dnf install postgresql
  • only distro PostgreSQL version
• Vanilla Repo
  • specific version
• Vendor Repo
  • specific version
• Other methods: binary tarball (*.tar.gz, only Percona???)
• Packages download
• Compile
• Container: CloudNativePG

Starting and Stopping PostgreSQL

• systemctl stop/start/status postgres
• journalctl -xeu postgres

PostgreSQL runs under O/S user postgres.

$ grep postgre /etc/passwd postgres:x:130:142:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

Process is called postgres mysql@chef:~ [mariadb-114, 3358]> ps -ef | grep postgres postgres 2085 1 0 11:08 ? 00:00:00 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf postgres 2100 2085 0 11:08 ? 00:00:00 postgres: 16/main: checkpointer postgres 2101 2085 0 11:08 ? 00:00:00 postgres: 16/main: background writer postgres 2103 2085 0 11:08 ? 00:00:00 postgres: 16/main: walwriter postgres 2104 2085 0 11:08 ? 00:00:00 postgres: 16/main: autovacuum launcher postgres 2105 2085 0 11:08 ? 00:00:00 postgres: 16/main: logical replication launcher

reload vs. restart

postgres@chef:~$ ss -tlpn State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 200 127.0.0.1:5432 0.0.0.0:* users:((“postgres”,pid=2085,fd=7)) LISTEN 0 200 [::1]:5432 [::]:* users:((“postgres”,pid=2085,fd=6))

postgres@chef:~$ lsof -p 2085 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME postgres 2085 postgres cwd DIR 8,17 4096 805617 /var/lib/postgresql/16/main postgres 2085 postgres rtd DIR 8,17 4096 2 / postgres 2085 postgres txt REG 8,17 10482400 7734049 /usr/lib/postgresql/16/bin/postgres postgres 2085 postgres DEL REG 0,1 3089 /dev/zero postgres 2085 postgres mem REG 0,27 26976 2 /dev/shm/PostgreSQL.3070043816 postgres 2085 postgres DEL REG 0,1 1 /SYSV000c4af1 postgres 2085 postgres 0r CHR 1,3 0t0 5 /dev/null postgres 2085 postgres 1w REG 8,17 1781 805648 /var/log/postgresql/postgresql-16-main.log postgres 2085 postgres 2w REG 8,17 1781 805648 /var/log/postgresql/postgresql-16-main.log postgres 2085 postgres 3u a_inode 0,15 0 1074 [signalfd] postgres 2085 postgres 4r FIFO 0,14 0t0 13515 pipe postgres 2085 postgres 5w FIFO 0,14 0t0 13515 pipe postgres 2085 postgres 6u IPv6 13521 0t0 TCP localhost:postgresql (LISTEN) postgres 2085 postgres 7u IPv4 13522 0t0 TCP localhost:postgresql (LISTEN) postgres 2085 postgres 8u unix 0x0000000000000000 0t0 13542 /var/run/postgresql/.s.PGSQL.5432 type=STREAM (LISTEN) postgres 2085 postgres 9u a_inode 0,15 0 1074 [eventpoll:3,6,7,8]

Shared Memory

$ ipcs -m

—— Shared Memory Segments ——– key shmid owner perms bytes nattch status 0x000c4af1 1 postgres 600 56 6

$ ipcs -mp

—— Shared Memory Creator/Last-op PIDs ——– shmid owner cpid lpid 1 postgres 2085 20053

$ ipcs -m -i 1

Shared memory Segment shmid=1 uid=130 gid=142 cuid=130 cgid=142 mode=0600 access_perms=0600 bytes=56 lpid=20183 cpid=2085 nattch=6 att_time=Tue Jul 1 14:35:42 2025 det_time=Tue Jul 1 14:35:42 2025 change_time=Tue Jul 1 11:08:36 2025

$ lsof | egrep ‘shm|COMM’ COMMAND PID TID TASKCMD USER FD TYPE DEVICE SIZE/OFF NODE NAME postgres 2085 postgres mem REG 0,27 26976 2 /dev/shm/PostgreSQL.3070043816 postgres 2100 postgres mem REG 0,27 26976 2 /dev/shm/PostgreSQL.3070043816 postgres 2101 postgres mem REG 0,27 26976 2 /dev/shm/PostgreSQL.3070043816 postgres 2103 postgres mem REG 0,27 26976 2 /dev/shm/PostgreSQL.3070043816 postgres 2104 postgres mem REG 0,27 1048576 3 /dev/shm/PostgreSQL.2124806318 postgres 2104 postgres mem REG 0,27 26976 2 /dev/shm/PostgreSQL.3070043816 postgres 2105 postgres mem REG 0,27 1048576 3 /dev/shm/PostgreSQL.2124806318 postgres 2105 postgres mem REG 0,27 26976 2 /dev/shm/PostgreSQL.3070043816

$ ls -l /dev/shm/PostgreSQL.* -rw——- 1 postgres postgres 1048576 Jul 1 11:08 /dev/shm/PostgreSQL.2124806318 -rw——- 1 postgres postgres 26976 Jul 1 11:08 /dev/shm/PostgreSQL.3070043816

$ free -m total used free shared buff/cache available Mem: 15674 6488 3923 906 6654 9186 Swap: 0 0 0