3 Architecture

postgres=# SELECT * FROM pg_shmem_allocations ;
                name                 |    off    |   size    | allocated_size
-------------------------------------+-----------+-----------+-------------
Buffer IO Locks                     | 140660096 |    524288 |         524288
 Buffer Descriptors                  |   5393792 |   1048576 |        1048576
 Backend SSL Status Buffer           | 146585088 |     42312 |          42368
 Async Queue Control                 | 147119360 |      2492 |           2560
 Wal Sender Ctl                      | 147112832 |      1280 |           1280
 AutoVacuum Data                     | 147104384 |      5368 |           5376
 PROCLOCK hash                       | 143136000 |      2904 |           2944
 FinishedSerializableTransactions    | 146097664 |        16 |            128
 XLOG Ctl                            |     53504 |   4208272 |        4208384
 Shared MultiXact State              |   5392640 |      1028 |           1152
…

postgres=# SELECT * FROM pg_stat_progress_analyze ;
-[ RECORD 1 ]-------------+---------------------
pid                       | 30932
datid                     | 13578
datname                   | postgres
relid                     | 16388
phase                     | computing statistics
sample_blks_total         | 54055
sample_blks_scanned       | 54055
ext_stats_total           | 0
ext_stats_computed        | 0
child_tables_total        | 0
child_tables_done         | 0
current_child_table_relid | 0

postgres=# SELECT * FROM pg_stat_progress_basebackup ;
-[ RECORD 1 ]--------+-------------------------
pid                  | 31080
phase                | streaming database files
backup_total         | 467913216
backup_streamed      | 302026752
tablespaces_total    | 1
tablespaces_streamed | 0

postgres=# SELECT name, blks_hit, blks_read, blks_written
FROM pg_stat_slru ;
      name       | blks_hit | blks_read | blks_written
-----------------+----------+-----------+--------------
 CommitTs        |        0 |         0 |            0
 MultiXactMember |        0 |         0 |            0
 MultiXactOffset |        0 |         0 |            0
 Notify          |        0 |         0 |            0
 Serial          |        0 |         0 |            0
 Subtrans        |        0 |         0 |            0
 Xact            |       14 |         0 |            0
 other           |        0 |         0 |            0
(8 rows)

postgres=> SELECT to_regcollation('"POSIX"') ;
 to_regcollation
-----------------
 "POSIX"
(1 row)

2 See 3.2.19 Functions

postgres=> EXPLAIN ANALYZE SELECT COUNT(*) FROM data1 GROUP BY c1 ;
                               QUERY PLAN
-----------------------------------------------------------------
HashAggregate
(cost=4304055.00..5029055.00
rows=10000000
width=14) (actual time=2703.993..16089.774 rows=10000000 loops=1)
   Group Key: c1
   Planned Partitions: 4  Batches: 87381  Memory Usage: 109kB  Disk
Usage: 268256kB
   ->  Seq Scan on data1  (cost=0.00..154055.00 rows=10000000
width=6) (actual time=0.025..606.455 rows=10000000 loops=1)
 Planning Time: 0.039 ms
 Execution Time: 16407.060 ms
(6 rows)

postgres=> EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM data1 ORDER
BY c1) t ORDER BY c1, c2;
                                   QUERY PLAN
-----------------------------------------------------------------
Incremental Sort  (cost=0.49..853808.44 rows=10000000 width=12)
(actual time=0.063..2253.672 rows=10000000 loops=1)
   Sort Key: data1.c1, data1.c2
   Presorted Key: data1.c1
   Full-sort Groups: 312500  Sort Method: quicksort  Average
Memory: 26kB  Peak Memory: 26kB
   ->
Index
Only
Scan
using
idx1_data1
on
data1
(cost=0.43..303808.43
rows=10000000
width=12)
(actual
time=0.045..891.341 rows=10000000 loo
ps=1)
         Heap Fetches: 0
 Planning Time: 0.258 ms
 Execution Time: 2495.751 ms
(8 rows)

$ pg_basebackup -D back
$ cat back/backup_manifest
{ "PostgreSQL-Backup-Manifest-Version": 1,
"Files": [
{ "Path": "backup_label", "Size": 226, "Last-Modified": "2020-09-
24 06:20:15 GMT", "Checksum-Algorithm": "CRC32C", "Checksum":
"0e45028a" },
{ "Path": "global/1262", "Size": 8192, "Last-Modified": "2020-09-
24 05:50:10 GMT", "Checksum-Algorithm": "CRC32C", "Checksum":
"6a2131f8" },
…
"WAL-Ranges": [
{
"Timeline":
1,
"Start-LSN":
"0/2D000028",
"End-LSN":
"0/2D0001E8" }
],
"Manifest-Checksum":
"8832de70e00fd7612125364088a945ff5cacca3befb4004eea255e2b498a0ca6
"}

If the backup is performed in tar format, the manifest file is not included in the tar file.

$ pg_basebackup --format=tar -D back
$ ls back
backup_manifest  base.tar  pg_wal.tar
$

$ pg_verifybackup back
backup successfully verified

postgres=> CREATE TABLE part1(c1 NUMERIC PRIMARY KEY, c2 VARCHAR(10))
PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES
FROM (0) TO (100000) ;
CREATE TABLE
postgres=> CREATE TABLE part1v2 PARTITION OF part1 FOR VALUES
FROM (100000) TO (200000) ;
CREATE TABLE
postgres=> CREATE PUBLICATION pub1 FOR TABLE part1 ;
CREATE PUBLICATION
postgres=> \dRp+ pub1
                            Publication pub1
 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
-------+------------+---------+---------+---------+-----------+----------
 demo  | f            | t        | t         | t        | t           | f
Tables:
    "public.part1"

postgres=> ALTER PUBLICATION pub1 SET (publish_via_partition_root = on) ;
ALTER PUBLICATION
postgres=> \dRp+ pub1
                            Publication pub1
 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
-------+------------+---------+---------+---------+-----------+----------
 demo  | f            | t         | t        | t        | t           | t
Tables:
    "public.part1"
# SUBSCRIPTION side
postgres=> CREATE TABLE part1(c1 NUMERIC PRIMARY KEY, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=remhost1 dbname=postgres
user=postgres password=<<PASSWORD>>' PUBLICATION pub1 ;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
postgres=#

postgres=> CREATE TABLE part1 (c1 INT, c2 INT, c3 VARCHAR(10))
PARTITION BY LIST(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN
(10) ;
CREATE TABLE
postgres=> CREATE OR REPLACE FUNCTION fnc1_part1() RETURNS TRIGGER
LANGUAGE plpgsql AS $$
   BEGIN
     NEW.c3 = 'TRIGGER';
     RETURN NEW;
 END; $$ ;
CREATE FUNCTION
postgres=> CREATE TRIGGER trg1_part1 BEFORE INSERT ON part1
FOR EACH ROW EXECUTE FUNCTION fnc1_part1() ;
CREATE TRIGGER
postgres=> INSERT INTO part1(c1, c2) VALUES (10, 20) ;
INSERT 0 1
postgres=> SELECT * FROM part1 ;
 c1 | c2 |   c3
----+----+---------
 10 | 20 | TRIGGER
(1 row)

postgres=> CREATE TABLE part2(c1 INT, c2 INT) PARTITION
BY LIST((part2)) ;
CREATE TABLE
postgres=> CREATE TABLE part2v1 PARTITION OF part2 FOR VALUES
IN ('(1, 2)') ;
CREATE TABLE
postgres=> CREATE TABLE part2v2 PARTITION OF part2 FOR VALUES
IN ('(2, 4)') ;
CREATE TABLE

2020-09-24 15:59:21.872 JST [31752] LOG:  automatic vacuum of table
"postgres.public.data1": index scans: 1
        pages: 0 removed, 54055 remain, 0 skipped due to pins, 0
skipped frozen
        tuples: 10000000 removed, 0 remain, 0 are dead but not yet
removable, oldest xmin: 514
        buffer usage: 234548 hits, 119331 misses, 130854 dirtied
        avg read rate: 2.780 MB/s, avg write rate: 3.048 MB/s
        system usage: CPU: user: 3.24 s, system: 4.13 s, elapsed:
335.39 s
        WAL usage: 244232 records, 93518 full page images, 303298890
bytes

$ ls -lid data
33832487 drwx------. 20 postgres postgres 4096 Sep 24 08:11 data
$ ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x02043e27 196610     postgres   600        56         6

In the above example, the i-node number is 33,832,487, which is 0x02043e27 when converted to
hexadecimal.

$ cat plpgsql.control
# plpgsql extension
comment = 'PL/pgSQL procedural language'
default_version = '1.0'
module_pathname = '$libdir/plpgsql'
relocatable = false
schema = pg_catalog
superuser = true
trusted = true

postgres=# CREATE USER demo PASSWORD '<<PASSWORD>>' ;
CREATE ROLE
postgres=# GRANT CREATE ON DATABASE postgres TO demo ;
GRANT
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> CREATE EXTENSION hstore ;
CREATE EXTENSION

The following extensions have the trusted setting set to 'on'.
• bool_plperl
• btree_gin
• btree_gist
• citext
• cube
• dict_int
• fuzzystrmatch
• hstore
• intarray.
• isn
• jsonb_plperl
• lo
• ltree
• pgcrypto
• pg_trgm
• plperl
• plpgsql
• seg
• tablefunc
• tcn
• tsm_system_rows
• tsm_system_time
• unaccent

postgres=# SELECT * FROM pg_replication_slots ;
-[ RECORD 1 ]-------+--------------------
slot_name           | pg_walreceiver_4273
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | t
active              | t
active_pid          | 4273
xmin                |
catalog_xmin        |
restart_lsn         | 0/390005A0
confirmed_flush_lsn |
wal_status          | reserved
safe_wal_size       |

postgres=> \dF
               List of text search configurations
   Schema   |    Name    |              Description
------------+------------+---------------------------------------
 pg_catalog | arabic     | configuration for arabic language
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | greek      | configuration for greek language << new
 pg_catalog | hungarian  | configuration for hungarian language
…