Spielereien
Kanu
Geopolitik
Business
TopoDB
POI
Klettersteigen
History
TransPool
Thermal Baden
Brokenstuben
Goldwaschen
Blog
Contact
/ home / computer / postgresql / postgresql-for-mysql-admins / .
.tar.gz)In general, PostgreSQL can be expected to work on these CPU architectures: x86, PowerPC, S/390, SPARC, ARM, MIPS, and RISC-V, including big-endian, little-endian, 32-bit, and 64-bit variants where applicable.
PostgreSQL can be expected to work on current versions of these operating systems: Linux, Windows, FreeBSD, OpenBSD, NetBSD, DragonFlyBSD, macOS, Solaris, and illumos. Other Unix-like systems may also work but are not currently being tested.
Sources:
In other cases, most Linux systems also make it easy to build from source (generic tarball).
Source:
Different Forks (flavours) of PostgreSQL (Licensen BSD)
We could also call them forks of “Vanilla PostgreSQL”.
PostgreSQL Releases:
apt/dnf install postgresql postgresql-common
$ git clone http://git.postgresql.org/git/postgresql.git
$ cd postgresql
$ ./configure
$ make -j 14
$ su
$ make install
$ adduser postgres
$ mkdir -p /usr/local/pgsql/data
$ chown postgres /usr/local/pgsql/data
$ su - postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
$ /usr/local/pgsql/bin/createdb test
$ /usr/local/pgsql/bin/psql test
Sources:
.tar.gz)Download from here. This could be interesting in combination with myEnv.
$ VERSION='18.4' # or 19beta1
$ wget https://ftp.postgresql.org/pub/source/v${VERSION}/postgresql-${VERSION}.tar.gz
$ tar xf postgresql-${VERSION}.tar.gz
$ cd postgresql-${VERSION}
$ ./configure --prefix=/tmp/postgresql-${VERSION}-x86_64
$ make -j 14
$ make install
$ tar -C /tmp -czf /tmp/postgresql-${VERSION}-x86_64.tar.gz postgresql-${VERSION}-x86_64
$ cp /tmp/postgresql-${VERSION}-x86_64.tar.gz /download
contrib)...
$ make world-bin -j 14
$ make install-world
$ ...
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# SELECT * FROM public.pg_stat_statements ORDER BY calls DESC LIMIT 10;
Building and Installation with Autoconf and Make
Recommendation: Use the packages provided by your distribution!!!
Integrate into PostgreSQL compile process:
$ VERSION='18.4'
$ wget https://ftp.postgresql.org/pub/source/v${VERSION}/postgresql-${VERSION}.tar.gz
$ tar xf postgresql-${VERSION}.tar.gz
$ cd postgresql-${VERSION}
$ ./configure --prefix=/tmp/postgresql-${VERSION}-x86_64
$ make world-bin -j 14
$ make install-world
Switch to plugin compile process:
$ cd /tmp
$ unzip /download/pg_stat_kcache-master.zip
$ cd pg_stat_kcache-master
$ export PATH=$PATH:/tmp/postgresql-${VERSION}/src/bin/pg_config
$ make
$ make install
Then finish the PostgreSQL packaging process
$ tar -C /tmp -czf /tmp/postgresql-${VERSION}-x86_64.tar.gz postgresql-${VERSION}-x86_64
$ cp /tmp/postgresql-${VERSION}-x86_64.tar.gz /download
Then replace PostgreSQL binaries with the new ones and configure accordingly:
#
# postgresql.conf
#
shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'
postgres=# CREATE EXTENSION pg_stat_kcache;
Sources:
pg_stat_kcache.tar.gz)This could be interesting in combination with myEnv.
$ INSTANCE_NAME='postgres-19'
$ cd product
$ tar xf /download/postgresql-19.dev-x86_64.tar.gz
$ mkdir -p /home/mysql/database/${INSTANCE_NAME}/data
$ cd /home/mysql/product/postgresql-19
$ ./bin/initdb --pgdata=/home/mysql/database/${INSTANCE_NAME}/data
$ ./bin/pg_ctl --pgdata=/home/mysql/database/${INSTANCE_NAME}/data --log=/home/mysql/database/${INSTANCE_NAME}/log/error.log --options='-p 5432' start
$ ./bin/psql --host=/tmp --port=5432 postgres
$ ./bin/createdb --host=/tmp --port=5432 test
$ ./bin/psql --host=/tmp --port=5432 test
See also: Installation Procedure
As of 2020 modules became obsolete and were integrated into extensions.
Check loaded and available extensions:
postgres=# \dx
List of installed extensions
Name | Version | Default version | Schema | Description
--------------------+---------+-----------------+------------+------------------------------------------------------------------------
pg_stat_kcache | 2.3.1 | 2.3.1 | public | Kernel statistics gathering
pg_stat_statements | 1.12 | 1.12 | public | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres=# SELECT * FROM pg_extension ORDER BY extname ASC;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
41006 | pg_stat_kcache | 40960 | 2200 | t | 2.3.1 | |
40961 | pg_stat_statements | 40960 | 2200 | t | 1.12 | |
13675 | plpgsql | 10 | 11 | f | 1.0 | |
postgres=# SELECT * FROM pg_available_extensions ORDER BY name ASC;
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+------------------------------------------------------------------------
amcheck | 1.5 | | functions for verifying relation integrity
autoinc | 1.0 | | functions for autoincrementing fields
...
tsm_system_time | 1.0 | | TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent | 1.1 | | text search dictionary that removes accents
Installing an extension:
postgres=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
NOTICE: extension "pg_stat_statements" already exists, skipping
CREATE EXTENSION
Installing a shared library:
postgres=# LOAD 'auto_explain';
LOAD
Sources:
PostgreSQL runs under O/S user postgres:
$ grep postgres /etc/passwd
postgres:x:130:142:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
Starting and stopping:
$ systemctl {stop|start|status} postgres
$ journalctl -xeu postgres
reload vs. restart?
The PostgreSQL process is called postgres:
$ 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
The PostgreSQL instancce listens on port 5432 (per default on 127.0.0.1 only:
$ 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))
Files in use by PostgreSQL:
$ 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]
In contrary to MySQL/MariaDB PostgreSQL is using Shared Memory (for shared_buffers):
$ ipcs -m
----- Shared Memory Segments ------
key shmid owner perms bytes nattch status
0x023a0001 327730 dba 600 56 10
$ ipcs -mp
----- Shared Memory Creator/Last-op PIDs ------
shmid owner cpid lpid
327730 dba 46247 50617
$ ipcs -m -i 327730
Shared memory Segment shmid=327730
uid=997 gid=988 cuid=997 cgid=988
mode=0600 access_perms=0600
bytes=56 lpid=50617 cpid=46247 nattch=10
att_time=Fri Jun 12 11:24:01 2026
det_time=Fri Jun 12 11:24:01 2026
change_time=Fri Jun 12 10:33:58 2026
$ lsof | egrep 'shm|COMM'
COMMAND PID TID TASKCMD USER FD TYPE DEVICE SIZE/OFF NODE NAME
postgres 46247 dba mem REG 0,28 29776 3 /dev/shm/PostgreSQL.739815862
postgres 46248 dba mem REG 0,28 29776 3 /dev/shm/PostgreSQL.739815862
postgres 46249 dba mem REG 0,28 29776 3 /dev/shm/PostgreSQL.739815862
postgres 46250 dba mem REG 0,28 29776 3 /dev/shm/PostgreSQL.739815862
postgres 46251 dba mem REG 0,28 29776 3 /dev/shm/PostgreSQL.739815862
postgres 46252 dba mem REG 0,28 29776 3 /dev/shm/PostgreSQL.739815862
postgres 46254 dba mem REG 0,28 29776 3 /dev/shm/PostgreSQL.739815862
postgres 46255 dba mem REG 0,28 1048576 4 /dev/shm/PostgreSQL.3501586536
postgres 46255 dba mem REG 0,28 29776 3 /dev/shm/PostgreSQL.739815862
postgres 46256 dba mem REG 0,28 1048576 4 /dev/shm/PostgreSQL.3501586536
postgres 46256 dba mem REG 0,28 29776 3 /dev/shm/PostgreSQL.739815862
postgres 46294 dba mem REG 0,28 1048576 4 /dev/shm/PostgreSQL.3501586536
postgres 46294 dba mem REG 0,28 29776 3 /dev/shm/PostgreSQL.739815862
$ ls -l /dev/shm/PostgreSQL.*
-rw------ 1 dba dba 1048576 Jun 12 10:34 /dev/shm/PostgreSQL.3501586536
-rw------ 1 dba dba 29776 Jun 12 10:33 /dev/shm/PostgreSQL.739815862
$ free -m
total used free shared buff/cache available
Mem: 31814 5904 18571 1158 8954 25909
Swap: 32483 0 32483