Spielereien
Kanu
Business
TopoDB
POI
Klettersteigen
History
TransPool
Thermal Baden
Brokenstuben
Goldwaschen
Blog
Contact
/ home / computer / postgresql / postgresql-for-mysql-admins / .
MySQL: bind_address
$ 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=6002,fd=7))
LISTEN 0 200 [::1]:5432 [::]:* users:(("postgres",pid=6002,fd=6))
Change listen_addresses = '*' in postgresql.conf and then systemctl restart postgresql. Additionally in pg_hba.conf:
host all all 0.0.0.0/0 md5
host all all ::/0 md5
followed by: systemctl reload postgresql.
Sources: Connection settings and The pg_hba.conf File
PostgreSQL is a multi-process architecture:
$ ps aux -L | grep -e postgres: -e VSZ -e main | grep -v grep
USER PID LWP %CPU NLWP %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1976 1976 0.0 1 0.1 225816 28744 ? Ss Jun20 0:08 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf
postgres 2133 2133 0.0 1 0.0 225952 8924 ? Ss Jun20 0:00 postgres: 16/main: checkpointer
postgres 2135 2135 0.0 1 0.0 225968 7144 ? Ss Jun20 0:00 postgres: 16/main: background writer
postgres 2173 2173 0.0 1 0.0 225816 10132 ? Ss Jun20 0:00 postgres: 16/main: walwriter
postgres 2174 2174 0.0 1 0.0 227420 8436 ? Ss Jun20 0:01 postgres: 16/main: autovacuum launcher
postgres 2175 2175 0.0 1 0.0 227396 8028 ? Ss Jun20 0:00 postgres: 16/main: logical replication launcher
postgres 369240 369240 0.0 1 0.0 228200 14888 ? Ss 15:10 0:00 postgres: 16/main: postgres postgres [local] idle
postgres 369250 369250 0.0 1 0.0 228200 14888 ? Ss 15:10 0:00 postgres: 16/main: postgres postgres [local] idle
postgres 369252 369252 0.0 1 0.0 228200 14888 ? Ss 15:10 0:00 postgres: 16/main: postgres postgres [local] idle
vs. MySQL multi-thread architecture:
$ ps aux -L | grep -e mysql-57 -e VSZ | grep -v grep
USER PID LWP %CPU NLWP %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 2573 2573 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2574 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2575 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2576 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2577 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2578 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2579 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2580 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2581 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2582 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2583 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2584 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2585 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:05 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2588 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:04 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2589 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:08 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2590 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2591 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2592 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2593 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2594 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2595 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2596 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2597 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2598 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2599 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2623 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2624 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql 2573 2665 0.0 28 1.0 1364832 172468 ? Sl Jun20 0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
Every connection to PostgreSQL spawns its own process:
Thus a connection pooler like PgBouncer makes sense or is even needed when having many connections.
/var/lib/postgresql/16 vs. /var/lib/mysql) : 1 Instance (postgres vs. mysqld) : n Databases (similar to MariaDB Catalog).Cluster/Instance
Databases: postgres template0 template1 customer1 customer2 customer3
Schemas: foodmart world crm
erp foodmart dwh
admin
Sources: Cluster, https://stackoverflow.com/questions/12908205/whats-a-postgresql-cluster-and-how-do-i-create-one
Note: While the MySQL CLI allows you to connect without specifying a database, PostgreSQL does not. You will not be able to connect to a database that does not exist. If you do not specify a database name (ie. just use the psql command), you will be connected to the database that has the same name as the user you are connecting to. If you do not specify a user, it will be assumed that your PostgreSQL user has the same name as the user you are logged into the server with.
$ psql -U username --> database username
$ psql --user=username --> database username
$ psql database_name --> database database_name
postgres=# \l
postgres=# \list
postgres=# \l+
postgres=# \list+
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------+---------+------------+--------------------------------------------
world | postgres | UTF8 | libc | en_GB.UTF-8 | en_GB.UTF-8 | | | | 7425 kB | pg_default |
postgres | postgres | UTF8 | libc | en_GB.UTF-8 | en_GB.UTF-8 | | | | 7095 MB | pg_default | default administrative connection database
template0 | postgres | UTF8 | libc | en_GB.UTF-8 | en_GB.UTF-8 | | | =c/postgres +| 7361 kB | pg_default | unmodifiable empty database
| | | | | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | libc | en_GB.UTF-8 | en_GB.UTF-8 | | | =c/postgres +| 7580 kB | pg_default | default template for new databases
| | | | | | | | postgres=CTc/postgres | | |
(4 rows)
postgres=# SELECT datname FROM pg_database;
datname
-----------
postgres
world
template1
template0
postgres is …???template1 is template for cloning a new database.template0 pristine database (one where no user-defined objects exist and where the system objects have not been altered) containing only the standard objects predefined by your version of PostgreSQL. This is useful if you wish to avoid copying any installation-local objects that might have been added to template1.postgres=# SELECT current_database();
current_database
------------------
postgres
See also prompt:
oli=# \connect postgres
oli=# \c postgres
You are now connected to database "postgres" as user "oli".
postgres=#
By default, the new database will be created by cloning the standard system database template1.
To create a database you must be a superuser or have the special CREATEDB privilege.
postgres=# CREATE DATABASE world;
postgres=# CREATE DATABASE dwh OWNER marketing TEMPLATE template0;
postgres=# DROP DATABASE world;
Source: CREATE DATABASE, DROP DATABASE
PostgreSQL offers schemas as a way to separate database objects. Every database gets a public schema by default that is owned by the superuser that created the database. This makes it difficult for regular users to drop, so avoid testing in the public schema unless you are planning to drop and recreate the database.
The maximum portability with other databases would be achieved by not using schemas at all.
oli=# -- display namespaces?
oli=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
oli=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+-------------------+----------------------------------------+------------------------
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
| | =U/pg_database_owner |
(1 row)
oli=# \dnS+
List of schemas
Name | Owner | Access privileges | Description
--------------------+-------------------+----------------------------------------+----------------------------------
information_schema | postgres | postgres=UC/postgres +|
| | =U/postgres |
pg_catalog | postgres | postgres=UC/postgres +| system catalog schema
| | =U/postgres |
pg_toast | postgres | | reserved schema for TOAST tables
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
| | =U/pg_database_owner |
(4 rows)
oli=# \dnS
List of schemas
Name | Owner
--------------------+-------------------
information_schema | postgres
pg_catalog | postgres
pg_toast | postgres
public | pg_database_owner
(4 rows)
pg_catalog contains the system tables and all the built-in data types, functions, and operators.pg_toast is for overflow tables (too long columns)???public there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should NOT use the public schema.information_schemapostgres=# CREATE SCHEMA world;
postgres=# DROP SCHEMA foodmart;
postgres=# DROP SCHEMA foodmart CASCADE;
oli=# SELECT current_schema();
current_schema
----------------
public
(1 row)
Full qualified name or via search_path:
oli=# SELECT * FROM test.test;
id | data | ts
----+------+----------------------------
1 | Bla | 2025-08-28 10:13:28.428007
2 | Bla | 2025-08-28 10:13:32.404196
3 | Bla | 2025-08-28 10:13:35.099629
(3 rows)
oli=# SELECT * FROM test;
ERROR: relation "test" does not exist
LINE 1: SELECT * FROM test;
^
By default, search_path is set to $user,public, meaning it will search in the schema named after the user you are logged in as first, then it will search in public. To query tables in schemas that are not in your current search_path or tables that have been shadowed by a schema with higher precedence, you must specify which schema you mean.
oli=# SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
oli=# SELECT * FROM pg_settings WHERE name='search_path';
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
-------------+-----------------+------+-------------------------------------------------+-----------------------------------------------------------------------+------------+---------+---------+---------+---------+---------+----------+-----------------+-----------------+------------+------------+-----------------
search_path | "$user", public | | Client Connection Defaults / Statement Behavior | Sets the schema search order for names that are not schema-qualified. | | user | string | default | | | | "$user", public | "$user", public | | | f
(1 row)
search_patholi=# SET search_path TO test,"$user",public;
oli=# SHOW search_path;
search_path
-----------------------
test, "$user", public
(1 row)
Note that this is not a permanent command, it only lasts for the duration of the current session. Making a permanent change for a given user requires updating their account settings.
Querying via search_path:
oli=# SELECT * FROM test;
id | data | ts
----+------+----------------------------
1 | Bla | 2025-08-28 10:13:28.428007
2 | Bla | 2025-08-28 10:13:32.404196
3 | Bla | 2025-08-28 10:13:35.099629
(3 rows)
Sources: Schemas
Sources: Hussein Nasser: Postgres Internal Architecture Explained
File: /etc/postgresql/16/main/postgresql.conf
pg_ctl reload vs. systemctl restart
ALTER SYSTEM SET <configuration_parameter> = <value>;
ALTER SYSTEM RESET <configuration_parameter>;
SELECT pg_reload_conf();
For the whole databsase cluster. Alterative to postgresql.conf
postgresql.auto.conf
$ pg_ctl reload
$ kill -SIGHUP $(pidof postgres)
$ systemctl restart postgres
postgres=# select name, setting, context, source, boot_val, reset_val, sourcefile, pending_restart from pg_settings limit 20;
name | setting | context | source | boot_val | reset_val | sourcefile | pending_restart
---------------------------------------+------------+------------+---------+-----------+-----------+------------+-----------------
allow_in_place_tablespaces | off | superuser | default | off | off | | f
allow_system_table_mods | off | superuser | default | off | off | | f
application_name | psql | user | client | | psql | | f
archive_cleanup_command | | sighup | default | | | | f
archive_command | (disabled) | sighup | default | | | | f
archive_library | | sighup | default | | | | f
archive_mode | off | postmaster | default | off | off | | f
archive_timeout | 0 | sighup | default | 0 | 0 | | f
array_nulls | on | user | default | on | on | | f
authentication_timeout | 60 | sighup | default | 60 | 60 | | f
autovacuum | on | sighup | default | on | on | | f
autovacuum_analyze_scale_factor | 0.1 | sighup | default | 0.1 | 0.1 | | f
autovacuum_analyze_threshold | 50 | sighup | default | 50 | 50 | | f
autovacuum_freeze_max_age | 200000000 | postmaster | default | 200000000 | 200000000 | | f
autovacuum_max_workers | 3 | postmaster | default | 3 | 3 | | f
autovacuum_multixact_freeze_max_age | 400000000 | postmaster | default | 400000000 | 400000000 | | f
autovacuum_naptime | 60 | sighup | default | 60 | 60 | | f
autovacuum_vacuum_cost_delay | 2 | sighup | default | 2 | 2 | | f
autovacuum_vacuum_cost_limit | -1 | sighup | default | -1 | -1 | | f
autovacuum_vacuum_insert_scale_factor | 0.2 | sighup | default | 0.2 | 0.2 | | f
(20 rows)
ALTER DATABASE <your_database_name> SET log_statement = 'all';
http://www.postgresql.org/docs/current/interactive/sql-altersystem.html
$ psql --user=postgres --command='SHOW config_file'
config_file
-----------------------------------------
/etc/postgresql/16/main/postgresql.conf
postgres=# SHOW data_directory;
data_directory
-----------------------------
/var/lib/postgresql/16/main
SHOW ALL;
postgres=# select name, setting from pg_settings where name like 'log\_%';
work_mem ~ tmp_table_size
Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files. Source https://www.postgresql.org/docs/current/runtime-config-resource.html
RAM usage:
shared_buffers analogous to MyISAM: key_buffer_size (or less close to innodb_buffer_pool_size). See also here
max_wal_size ~ innodb_log_file_size / innodb_redo_log_capacity?
synchronous_commit ~ innodb_flush_log_at_trx_commit - See also here
See slides from Swiss PG day 2025 (Luigi Nardi)
Affects Optimizer choice:
random_page_cost See also here
(https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-RANDOM-PAGE-COST)
See also for MySQL: The Optimizer Cost Model and MariaDB: The Optimizer Cost Model from MariaDB 11.0
Default: 4.0 -> SSD 2.0, all in RAM/Cache -> 1.0?
checkpoint_timeout 300s / 5min
wal_segment_size 16MB
checkpoint_completion_target 0.9 / 90%
Based on the logs, your checkpoints are triggered by filling WAL. I see
your checkpoints happen every 30 - 40 seconds, and you only have 64
segments.
So to get checkpoints checkpoints triggered by timeout (which I assume
is 5 minutes, because you have not mentioned checkpoint_timeout), you
need to increase checkpoint_segments enough to hold 5 minutes worth of WAL.
That means 300/30 * 64, i.e. roughly 640 segments (it's likely an
overestimate, due to full page writes, but well).
Sources:
mysql> SHOW GLOBAL VARIABLES LIKE '...';
postgres=# SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%checkpoint%' OR name LIKE '%wal%';
name | setting | unit
-------------------------------+-----------+------
auto_explain.log_wal | off |
checkpoint_completion_target | 0.9 |
checkpoint_flush_after | 32 | 8kB
checkpoint_timeout | 300 | s
checkpoint_warning | 30 | s
log_checkpoints | on |
max_slot_wal_keep_size | -1 | MB
max_wal_senders | 10 |
max_wal_size | 1024 | MB
min_wal_size | 80 | MB
track_wal_io_timing | off |
wal_block_size | 8192 |
wal_buffers | 512 | 8kB
wal_compression | off |
wal_consistency_checking | |
wal_decode_buffer_size | 524288 | B
wal_init_zero | on |
wal_keep_size | 0 | MB
wal_level | replica |
wal_log_hints | off |
wal_receiver_create_temp_slot | off |
wal_receiver_status_interval | 10 | s
wal_receiver_timeout | 60000 | ms
wal_recycle | on |
wal_retrieve_retry_interval | 5000 | ms
wal_segment_size | 16777216 | B
wal_sender_timeout | 60000 | ms
wal_skip_threshold | 2048 | kB
wal_sync_method | fdatasync |
wal_writer_delay | 200 | ms
wal_writer_flush_after | 128 | 8kB
(31 rows)
Reload, restart, set system…