3 Architecture

postgres=# SELECT * FROM pg_ident_file_mapping ORDER BY 1 ;
line_number | map_name | sys_name | pg_username | error
-------------+----------+----------+-------------+-------
          43 | map1     | sysname1 | pgname1     |
          44 | map2     | sysname2 | pgname2     |
(2 rows)

postgres=> SELECT * FROM pg_publication_namespace ;
  oid  | pnpubid | pnnspid |
-------+---------+---------+
 16411 |   16410 |    2200 |
(1 row)

postgres=# SELECT * FROM pg_stat_subscription_stats ;
-[ RECORD 1 ]-----+------
subid             | 16395
subname           | sub1
apply_error_count | 0
sync_error_count  | 23
stats_reset       |

postgres=# GRANT pg_read_all_stats TO demo ;
GRANT ROLE
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> SELECT COUNT(*) FROM pg_backend_memory_contexts ;
 count
-------
   122
(1 row)

postgres=> CREATE TABLE repl1(c1 INT PRIMARY KEY, c2 INT, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE PUBLICATION pub1 FOR TABLE repl1(c1, c2) ;
CREATE PUBLICATION
postgres=> SELECT oid, prpubid, prrelid, prattrs FROM pg_publication_rel ;
-[ RECORD 1 ]--
oid     | 16394
prpubid | 16393
prrelid | 16388
prattrs | 1 2

When executing an UPDATE or DELETE statement, the column specification must include all
replica identities (primary key, etc.). In the example below, the UPDATE statement is in error by
specifying a column that does not include the primary key in the publication.

postgres=> CREATE TABLE repl2(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE PUBLICATION pub2 FOR TABLE repl2(c2) ;
CREATE PUBLICATION
postgres=> UPDATE repl2 SET c2='update' WHERE c1=100 ;
ERROR:  cannot update table "repl2"
DETAIL:  Column list used by the publication does not cover the replica
identity.

postgres=> ALTER PUBLICATION pub1 SET TABLE repl1 WHERE (c1 < 1000) ;
ALTER PUBLICATION

In order to execute UPDATE or DELETE statements on the table to be replicated, the WHERE clause
must include a part of the replica identity (primary key, etc.).

postgres=> CREATE TABLE repl3(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE PUBLICATION pub3 FOR TABLE repl3 WHERE (c2='update') ;
CREATE PUBLICATION
postgres=> UPDATE repl3 SET c2='modify' WHERE c1=1000 ;
ERROR:  cannot update table "repl3"
DETAIL:  Column used in the publication WHERE expression is not part of the
replica identity.
postgres=> DELETE FROM repl3 WHERE c1=1000 ;
ERROR:  cannot delete from table "repl3"
DETAIL:  Column used in the publication WHERE expression is not part of the
replica identity.

The conditional statement that can be specified in the WHERE clause must be statically determined.
Therefore, user-defined functions and MUTABLE-specified functions cannot be specified.

postgres=> CREATE PUBLICATION pub4 FOR TABLE repl1 WHERE (c1 = random()) ;
ERROR:  invalid publication WHERE expression
LINE 1: ...EATE PUBLICATION pub4 FOR TABLE repl1 WHERE (c1 = random());
                                                             ^
DETAIL:  User-defined or built-in mutable functions are not allowed.

postgres=> \d repl1
                      Table "public.repl1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null |
 c2     | character varying(10) |           |          |
Indexes:
    "repl1_pkey" PRIMARY KEY, btree (c1)
Publications:
    "pub1" WHERE (c1 < 1000)

postgres=# CREATE PUBLICATION pub5 FOR TABLES IN SCHEMA schema1 ;
CREATE PUBLICATION
postgres=# \dRp
                               List of publications
 Name |  Owner   | All tables | Inserts | Updates | Deletes | Truncates | …
------+----------+------------+---------+---------+---------+-----------+-…
pub5 | postgres | f          | t       | t       | t       | t         | f
(1 row)

The pg_publication_namespace catalog has been added with this implementation.

postgres=# ALTER SUBSCRIPTION sub1 SKIP (LSN = '0/30B51E0') ;
ALTER SUBSCRIPTION
postgres=# SELECT subskiplsn FROM pg_subscription ;
 subskiplsn
------------
 0/30B51E0
(1 row)

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=remsvr1, dbname=postgres'
PUBLICATION pub1 WITH (disable_on_error=true, two_phase=true) ;
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

postgres=> EXPLAIN SELECT DISTINCT c2 FROM data1 ;
                                 QUERY PLAN
------------------------------------------------------------------------------
 Unique  (cost=11614.55..11614.56 rows=1 width=6)
   ->  Sort  (cost=11614.55..11614.56 rows=2 width=6)
         Sort Key: c2
         ->  Gather  (cost=11614.33..11614.54 rows=2 width=6)
               Workers Planned: 2
               ->  HashAggregate  (cost=10614.33..10614.34 rows=1 width=6)
                     Group Key: c2
                     ->  Parallel Seq Scan on data1  (cost=0.00..9572.67 ro…
(8 rows)

postgres=# SELECT name, setting, vartype, enumvals FROM pg_settings
WHERE name='wal_compression' ;
-[ RECORD 1 ]---------------
name     | wal_compression
setting  | off
vartype  | enum
enumvals | {pglz,lz4,zstd,on,off}

To use value lz4 or zstd, it must be compiled with --with-lz4 and --with-zstd specified when executing
'configure' command.

postgres=# SELECT * FROM pg_settings WHERE name LIKE 'archive_library' ;
-[ RECORD 1 ]---+-------------------------------------------------------------
name            | archive_library
setting         | basic_archive
unit            |
category        | Write-Ahead Log / Archiving
short_desc      | Sets the library that will be called to archive a WAL file.
extra_desc     | An empty string indicates that "archive_command" should be
used.
context         | sighup
vartype         | string
source          | configuration file
min_val         |
max_val         |
enumvals        |
boot_val        |
reset_val       | basic_archive
sourcefile      | /home/postgres/data/postgresql.conf
sourceline      | 255
pending_restart | f
postgres=# SHOW basic_archive.archive_directory ;
-[ RECORD 1 ]-------------------+----------------------
basic_archive.archive_directory | /home/postgres/arch

If both archive_library and archive_command are specified, archive_library is used.

$ initdb --locale-provider=icu --icu-locale=en --encoding=utf8 -D data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with this locale configuration:
  provider:    icu
  ICU locale:  en
  LC_COLLATE:  en_US.utf8
  LC_CTYPE:    en_US.utf8
  LC_MESSAGES: en_US.utf8
  LC_MONETARY: en_US.utf8
  LC_NUMERIC:  en_US.utf8
  LC_TIME:     en_US.utf8
The default text search configuration will be set to "english".
…

Added LOCALE_PROVIDER to specify the locale provider and ICU_LOCALE to specify the locale
name to the options of the CREATE DATABASE statement.

postgres=# CREATE DATABASE icudb1 LOCALE_PROVIDER=icu ICU_LOCALE=en
TEMPLATE=template0 ;
CREATE DATABASE
postgres=# SELECT datname, daticulocale FROM pg_database
WHERE datname='icudb1' ;
 datname | daticulocale
---------+--------------
 icudb1  | en
(1 row)

The psql command outputs ICU Locale and Locale Provider in the output of the \l command.

postgres=> \l
                                                List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider…
-----------+----------+----------+------------+------------+------------+--------------- …
postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | en         | icu             …
template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | en         | icu             …
template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | en         | icu             …
(3 rows)

postgres=# SELECT * FROM pg_shmem_allocations WHERE name LIKE 'Shared%Stats' ;
        name         |    off    |  size  | allocated_size
---------------------+-----------+--------+----------------
 Shared Memory Stats | 147331584 | 263312 |         263424
(1 row)
postgres=# SELECT name, total_bytes, free_bytes, used_bytes FROM
pg_backend_memory_contexts WHERE name LIKE 'PgStat%' ;
          name          | total_bytes | free_bytes | used_bytes
------------------------+-------------+------------+------------
 PgStat Shared Ref Hash |        7224 |        680 |       6544
 PgStat Shared Ref      |        4096 |       1432 |       2664
 PgStat Pending         |        4096 |       2168 |       1928
(3 rows)

postgres=> CREATE EXTENSION btree_gist ;
CREATE EXTENSION
postgres=> CREATE TABLE gist1(c1 INT, c2 BOOL, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE INDEX idx1_gist1 ON gist1 USING gist (c2) ;
CREATE INDEX

postgres=# CREATE USER check1 PASSWORD 'check1' ;
CREATE ROLE
postgres=# \connect postgres check1
You are now connected to database "postgres" as user "check1".
postgres=> CHECKPOINT ;
ERROR:  must be superuser or have privileges of pg_checkpoint to do CHECKPOINT
postgres=> \connect postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# GRANT pg_checkpoint TO check1 ;
GRANT ROLE
postgres=# \connect postgres check1
You are now connected to database "postgres" as user "check1".
postgres=> CHECKPOINT ;
CHECKPOINT

postgres=# SELECT * FROM pg_get_wal_resource_managers() ;
 rm_id |      rm_name      | rm_builtin
-------+-------------------+------------
     0 | XLOG              | t
     1 | Transaction       | t
     2 | Storage           | t
     3 | CLOG              | t
…

typedef void (*object_access_hook_type_str) (ObjectAccessType access,
                                        Oid classId,
                                        const char *objectStr,
                                        int subId,
                                        void *arg);
extern PGDLLIMPORT object_access_hook_type_str object_access_hook_str;

typedef void (*shmem_request_hook_type) (void);
extern PGDLLIMPORT shmem_request_hook_type shmem_request_hook;
extern PGDLLIMPORT bool process_shmem_requests_in_progress;