Spielereien
Kanu
Geopolitik
Business
TopoDB
POI
Klettersteigen
History
TransPool
Thermal Baden
Brokenstuben
Goldwaschen
Blog
Contact
/ home / computer / postgresql / postgresql-for-mysql-admins / .
Term “instance” is not really common in PostgreSQL/MySQL terminology: My understanding of instance is: PostgreSQL processes belonging together / MySQL process.
PostgreSQL is a multi-process architecture:
$ ps -eLf | grep -e postgres -e UID
UID PID PPID LWP C NLWP STIME TTY TIME CMD
dba 89500 1495 89500 0 1 Jun12 ? 00:00:01 /home/dba/product/postgresql-18.4-x86_64/bin/postgres -p 5434
dba 89501 89500 89501 0 1 Jun12 ? 00:00:01 postgres: io worker 0
dba 89502 89500 89502 0 1 Jun12 ? 00:00:00 postgres: io worker 1
dba 89503 89500 89503 0 1 Jun12 ? 00:00:00 postgres: io worker 2
dba 89504 89500 89504 0 1 Jun12 ? 00:00:00 postgres: checkpointer
dba 89505 89500 89505 0 1 Jun12 ? 00:00:00 postgres: background writer
dba 89507 89500 89507 0 1 Jun12 ? 00:00:00 postgres: walwriter
dba 89508 89500 89508 0 1 Jun12 ? 00:00:00 postgres: autovacuum launcher
dba 89509 89500 89509 0 1 Jun12 ? 00:00:00 postgres: logical replication launcher
vs. MySQL multi-thread architecture:
$ ps -eLf | grep -e mysql -e UID
UID PID PPID LWP C NLWP STIME TTY TIME CMD
dba 4842 1495 4842 0 46 Jun11 ? 00:00:00 bin/mysqld --defaults-file=/home/dba/database/mysql-97/etc/my.cnf --basedir=/home/dba/product/mysql-9.7 --datadir=/home/dba/database/mysql-97/data --user=dba
dba 4842 1495 4845 0 46 Jun11 ? 00:00:08 bin/mysqld --defaults-file=/home/dba/database/mysql-97/etc/my.cnf --basedir=/home/dba/product/mysql-9.7 --datadir=/home/dba/database/mysql-97/data --user=dba
dba 4842 1495 4846 0 46 Jun11 ? 00:00:08 bin/mysqld --defaults-file=/home/dba/database/mysql-97/etc/my.cnf --basedir=/home/dba/product/mysql-9.7 --datadir=/home/dba/database/mysql-97/data --user=dba
dba 4842 1495 4847 0 46 Jun11 ? 00:00:08 bin/mysqld --defaults-file=/home/dba/database/mysql-97/etc/my.cnf --basedir=/home/dba/product/mysql-9.7 --datadir=/home/dba/database/mysql-97/data --user=dba
...
dba 4842 1495 4894 0 46 Jun11 ? 00:00:00 bin/mysqld --defaults-file=/home/dba/database/mysql-97/etc/my.cnf --basedir=/home/dba/product/mysql-9.7 --datadir=/home/dba/database/mysql-97/data --user=dba
dba 4842 1495 4895 0 46 Jun11 ? 00:00:00 bin/mysqld --defaults-file=/home/dba/database/mysql-97/etc/my.cnf --basedir=/home/dba/product/mysql-9.7 --datadir=/home/dba/database/mysql-97/data --user=dba
dba 4842 1495 5410 0 46 Jun11 ? 00:00:05 bin/mysqld --defaults-file=/home/dba/database/mysql-97/etc/my.cnf --basedir=/home/dba/product/mysql-9.7 --datadir=/home/dba/database/mysql-97/data --user=dba
Every connection to PostgreSQL spawns its own process:
Thus a connection pooler like PgBouncer makes sense or is even needed when having many connections (> 100).
The PostgreSQL term “Cluster” has NOTHING to do with high-availability (HA) Clusters (Replication Cluster, fail-over Cluster, Galera Cluster, InnoDB Cluster, etc.)!
A database cluster is a collection of databases that is managed by a single instance of a running database server.
1 Cluster (/var/lib/postgresql/18 vs. /var/lib/mysql)
= 1 Instance (postgres vs. mysqld)
contains: n Databases (MySQL does NOT have this concept, similar to MariaDB Catalog, which is not production ready yet).
contains: n Schemata (MySQL/MariaDB calls this often a database but in fact it is a schema).
PostgreSQL Cluster/Instance:
Databases: postgres template0 template1 customer1 customer2 customer3 dev test prod
Schemata: (information_schema) - (information_schema) (information_schema) (information_schema) (information_schema) (information_schema) (information_schema) (information_schema)
(pg_catalog) - (pg_catalog) (pg_catalog) (pg_catalog) (pg_catalog) (pg_catalog) (pg_catalog) (pg_catalog)
(pg_toast) - (pg_toast) (pg_toast) (pg_toast) (pg_toast) (pg_toast) (pg_toast) (pg_toast)
public - public public public public public public public
foodmart world crm webshop webshop webshop
erp foodmart dwh customer1 customer1 customer1
admin
() hidden by default
The postgres database is only there for compatibility reasons with applications and can be dropped. You should create your own database.
The public schema can be dropped and should not be used for application data. On should create an own schema.
“A schema is essentially a namespace” (similar to MySQL databases/schemata (which are synonyms)).
1 Database : n Schemata
As a result we get:
PostgreSQL: 1 Cluster : 1 Instance : n Databases : m Schemas
MySQL: 1 Instance : n Databases/Schemata
MariaDB: 1 Instance : n Catalogs : m Databases/Schemata
Sources:
A PostgreSQL cluster can contain many databases.
Connecting to a database:
$ psql --user=username # --> database postgres, you can see this in the prompt
postgres=#
$ psql -U<username> # --> short form, caution: MySQL is -u!
postgres=#
$ psql test # --> database test as O/S user, you can see this in the prompt
test=# select current_user;
current_user
--------------
dba
$ psql --user=oli test # --> database test as user oli, you can see this in the prompt
test=> select current_user;
current_user
--------------
oli
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.
List databases:
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 | | |
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.List current database:
postgres=# SELECT current_database();
current_database
------------------
postgres
Change database connection, see also prompt:
test=# \connect postgres
test=# \c postgres
You are now connected to database "postgres" as user "oli".
postgres=#
Note: You connect to a database, not to a schema!
Create and drop a database:
postgres=# CREATE DATABASE world;
postgres=# CREATE DATABASE dwh OWNER marketing TEMPLATE template0;
postgres=# DROP DATABASE world;
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.
Source:
PostgreSQL offers schemata 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.
List schemas:
test=# -- display namespaces?
test=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
test=# \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)
test=# \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)
test=# \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_schemaCreate and drop schemas:
postgres=# CREATE SCHEMA world;
postgres=# DROP SCHEMA foodmart;
postgres=# DROP SCHEMA foodmart CASCADE;
List current schema:
test=# SELECT current_schema();
current_schema
----------------
public
(1 row)
Querying data:
Note: You connect to a database, not to a schema!
Full qualified name or via search_path:
test=# 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
test=# 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.
test=# SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
test=# 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
Changing your current search_path:
test=# SET search_path TO test,"$user",public;
test=# 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:
test=# 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:
Sources: