2020-09-24 23:27:12.495 JST,,,30744,,5ec6194a.7818,1,,2020-05-21
15:01:46 JST,,0,LOG,00000,"database system was shut down at 2020-
05-21 15:01:29 JST",,,,,,,,,"","startup"
2020-09-24 23:27:12.495 JST,,,30734,,5ec61949.780e,6,,2020-05-21
15:01:45 JST,,0,LOG,00000,"database system is ready to accept
connections",,,,,,,,,"","postmaster"
2020-09-24
23:27:12.495
JST,"postgres","postgres",30753,"[local]",5ec61953.7821,1,"SELECT"
,2020-05-21 15:01:55 JST,3/2,0,ERROR,42P01,"relation ""notexists""
does not exist",,,,,,"SELECT * FROM notexists;",15,,"psql","client
backend"

postgres=> BEGIN ;
BEGIN
postgres=*> -- In the active transaction
postgres=*> ERROR ;
ERROR: syntax error at or near "ERROR"
LINE 1: ERROR
        ^
postgres=!> -- In the failed transaction
postgres=!> ROLLBACK ;
ROLLBACK

postgres=> SET lc_time='ru_RU' ;
SET
postgres=> SELECT to_date('01 фев 2020', 'DD TMMON YYYY') ;
  to_date
------------
 2020-02-01
(1 row)

postgres=> CREATE TABLE part1 (c1 INT, c2 INT)
PARTITION BY RANGE(((c1, c2))) ;
ERROR:  partition key column 1 has pseudo-type record

postgres=> SELECT 'ABC' SIMILAR TO 'ABC' ESCAPE NULL ;
 ?column?
----------
 t
(1 row)

postgres=> \pset null null
Null display is "null".
postgres=> SELECT 'ABC' SIMILAR TO 'ABC' ESCAPE NULL ;
?column?
----------
 null
(1 row)

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
…

postgres=> ALTER FUNCTION func1 DEPENDS ON EXTENSION cube ;
ALTER FUNCTION
postgres=> ALTER FUNCTION func1 NO DEPENDS ON EXTENSION cube ;
ALTER FUNCTION

postgres=> CREATE TABLE data1(c1 INT, c2 INT, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE STATISTICS stat1 ON c1, c2 FROM data1 ;
CREATE STATISTICS
postgres=> ALTER STATISTICS stat1 SET STATISTICS 10000 ;
ALTER STATISTICS
postgres=> SELECT stxname, stxstattarget FROM pg_statistic_ext
WHERE stxname='stat1' ;
 stxname | stxstattarget
---------+---------------
 stat1   |         10000
(1 row)
postgres=> \d data1
                      Table "public.data1"
 Column |         Type          | Collation | Nullable | Default
--------+--------------------+-----------+----------+---------
 c1     | integer               |             |          |
 c2     | integer               |             |          |
 c3     | character varying(10) |          |          |
Statistics objects:
    "public"."stat1" (ndistinct, dependencies, mcv) ON c1, c2 FROM
data1; STATISTICS 10000

postgres=> CREATE TABLE gen1(c1 INT, c2 INT, c3 INT
GENERATED ALWAYS AS (c1 + c2) STORED) ;
CREATE TABLE
postgres=> \d gen1
                              Table "public.gen1"
 Column |  Type   | Collation | Nullable |               Default
--------+---------+-----------+----------+--------------------------
c1     | integer |           |          |
 c2     | integer |           |          |
 c3     | integer |           |          | generated always as (c1 + c2)
stored
postgres=> INSERT INTO gen1(c1, c2) VALUES (100, 200) ;
INSERT 0 1
postgres=> ALTER TABLE gen1 ALTER COLUMN c3
DROP EXPRESSION IF EXISTS ;
ALTER TABLE
postgres=> \d gen1
                Table "public.gen1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 c1     | integer |           |          |
 c2     | integer |           |          |
 c3     | integer |           |          |
postgres=> SELECT * FROM gen1 ;
 c1  | c2  | c3
-----+-----+-----
 100 | 200 | 300
(1 row)

postgres=> CREATE TABLE attr1(c1 CHAR(10)) ;
CREATE TABLE
postgres=> \d+ attr1
                                      Table "public.attr1"
 Column |     Type      | Collation | Nullable | Default | Storage  | …
--------+-------------+-----------+----------+---------+----------+ …
 c1     | character(10) |            |            |           | extended | …
Access method: heap
postgres=> CREATE INDEX idx1_attr1 ON attr1(c1) ;
CREATE INDEX
postgres=> ALTER TABLE attr1 ALTER COLUMN c1 SET STORAGE EXTERNAL ;
ALTER TABLE
postgres=> \d+ idx1_attr1
                      Index "public.idx1_attr1"
 Column |     Type      | Key? | Definition | Storage  | Stats target
--------+-------------+------+------------+----------+--------------
 c1     | character(10) | yes | c1           | external |
btree, for table "public.attr1"

postgres=# ALTER TYPE box SET (SEND = myboxsend) ;
ALTER TYPE

postgres=> CREATE VIEW customer_name AS SELECT c_customer_id,
c_first_name || ',' || c_last_name c_full_name FROM customer ;
CREATE VIEW
postgres=> \d customer_name
                  View "public.customer_name"
    Column     |     Type      | Collation | Nullable | Default
---------------+---------------+-----------+----------+---------
 c_customer_id | character(16) |           |          |
 c_full_name   | text          |           |          |
postgres=> ALTER VIEW customer_name RENAME COLUMN c_full_name TO full_name ;
ALTER VIEW
postgres=> \d customer_name
                  View "public.customer_name"
    Column     |     Type      | Collation | Nullable | Default
---------------+---------------+-----------+----------+---------
 c_customer_id | character(16) |           |          |
 full_name     | text          |           |          |

postgres=# CREATE DATABASE localedb1 WITH LOCALE='ja_JP.eucjp' ENCODING='eucjp'
TEMPLATE=template0 ;
CREATE DATABASE
postgres=# \l localedb1
                                List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges
-----------+----------+----------+-------------+-------------+-----------------
 localedb1 | postgres | EUC_JP   | ja_JP.eucjp | ja_JP.eucjp |
(1 row)

postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10), c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> INSERT INTO data1 VALUES (generate_series(1, 1000000),
'data1', 'data1') ;
INSERT 0 1000000
postgres=> CREATE INDEX idx1_dup ON data1(c2)
WITH (deduplicate_items = off) ;
CREATE INDEX
postgres=> CREATE INDEX idx2_dedup ON data1(c3)
WITH (deduplicate_items = on) ;
CREATE INDEX
postgres=> \d+ idx1_dup
                           Index "public.idx1_dup"
 Column |         Type          | Key? | Definition | Storage  | Stats target
--------+-----------------------+------+------------+----------+--
 c2     | character varying(10) | yes  | c2         | extended |
btree, for table "public.data1"
Options: deduplicate_items=off

postgres=> SELECT pg_size_pretty(pg_relation_size('idx1_dup')) ;
 pg_size_pretty
----------------
 21 MB
(1 row)
postgres=> SELECT pg_size_pretty(pg_relation_size('idx2_dedup')) ;
 pg_size_pretty
----------------
 6792 kB
(1 row)

postgres=> CREATE TABLE data1(c1 NUMERIC, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> ALTER TABLE data1 SET (toast.vacuum_index_cleanup = off) ;
ALTER TABLE
postgres=> ALTER TABLE data1 SET (autovacuum_vacuum_insert_threshold = 10000) ;
ALTER TABLE
postgres=> ALTER TABLE data1 SET (autovacuum_vacuum_insert_scale_factor =
0.1) ;
ALTER TABLE

postgres=# CREATE TABLESPACE ts1 LOCATION '/home/postgres/ts1'
WITH (maintenance_io_concurrency = 20) ;
CREATE TABLESPACE
postgres=# ALTER TABLESPACE ts1 SET (maintenance_io_concurrency = 30) ;
ALTER TABLESPACE
postgres=# \db+ ts1
                                                List of tablespaces
 Name |  Owner   |      Location      | Access privileges |             Options
|  Size   | Description
------+----------+--------------------+-------------------+--------------
-------------------+---------+-------------
 ts1  | postgres | /home/postgres/ts1 |                   |
{maintenance_io_concurrency=30} | 0 bytes |
(1 row)

postgres=# DROP DATABASE demodb WITH (FORCE) ;
DROP DATABASE
postgres=# DROP DATABASE postgres WITH (FORCE) ;
ERROR:  cannot drop the currently open database

postgres=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM data1 WHERE c1
= 10000 ;
                                   QUERY PLAN
-----------------------------------------------------------------
Index Scan using idx1_data1 on data1  (cost=0.42..8.44 rows=1
width=16) (actual time=0.029..0.030 rows=1 loops=1)
   Index Cond: (c1 = 10000)
   Buffers: shared hit=1 read=3
 Planning:
   Buffers: shared hit=17 read=1
 Planning Time: 1.380 ms
 Execution Time: 0.044 ms
(7 rows)

If ANALYZE clause is not specified, the planner's buffer usage is output.

postgres=> EXPLAIN (BUFFERS) SELECT * FROM data1 WHERE c1=100 ;
                                QUERY PLAN
-----------------------------------------------------------------
 Index Scan using idx1_data1 on data1  (cost=0.43..19.68 rows=4
width=16)
   Index Cond: (c1 = 100)
 Planning:
   Buffers: shared hit=93 read=3
(4 rows)

postgres=> EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM data1 WHERE c1 < 10000 ;
                                                             QUERY PLAN
-----------------------------------------------------------------------------
 Gather
(cost=0.00..107056.40
rows=9188
width=12)
(actual
time=12.326..410.535 rows=9999 loops=1)
   Output: c1, c2
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on public.data1  (cost=0.00..106137.60 rows=3828
width=12) (actual time=228.802..360.446 rows=3333 loops=3)
         Output: c1, c2
         Filter: (data1.c1 < '10000'::numeric)
         Rows Removed by Filter: 3330000
         Worker 0:  actual time=330.989..330.989 rows=0 loops=1
           JIT:
             Functions: 2
             Options: Inlining false, Optimization false, Expressions true,
Deforming true
             Timing: Generation 2.370 ms, Inlining 0.000 ms, Optimization 1.562
ms, Emission 25.957 ms, Total 29.889 ms
         Worker 1:  actual time=343.317..343.317 rows=0 loops=1
           JIT:
             Functions: 2
             Options: Inlining false, Optimization false, Expressions true,
Deforming true
             Timing: Generation 1.655 ms, Inlining 0.000 ms, Optimization 1.265
ms, Emission 18.974 ms, Total 21.894 ms
…

postgres=> EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM data1 ORDER BY c1 ;
                                                                  QUERY PLAN
----------------------------------------------------------------------------
Gather Merge  (cost=752323.83..1726047.95 rows=8345624 width=12) (actual
time=1471.139..4003.430 rows=10000000 loops=1)
   Output: c1, c2
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=751323.81..761755.84 rows=4172812 width=12) (actual
time=1328.103..1849.320 rows=3333333 loops=3)
         Output: c1, c2
         Sort Key: data1.c1
         Sort Method: external merge  Disk: 74864kB
         Worker 0:  actual time=1466.829..1989.939 rows=3335746 loops=1
           Sort Method: external merge  Disk: 75096kB
         Worker 1:  actual time=1360.627..1885.937 rows=3338879 loops=1
           Sort Method: external merge  Disk: 75168kB
         ->  Parallel Seq Scan on public.data1  (cost=0.00..149837.12
rows=4172812 width=12) (actual time=60.222..279.624 rows=3333333 loops=3)
               Output: c1, c2
               Worker 0:  actual time=58.974..280.404 rows=3335746 loops=1
               Worker 1:  actual time=58.948..277.968 rows=3338879 loops=1
 Planning Time: 0.056 ms
 Execution Time: 4261.051 ms
(18 rows)

postgres=> EXPLAIN (ANALYZE, WAL) DELETE FROM data1 WHERE c1 = 20000 ;
                                                QUERY PLAN
--------------------------------------------------------------------
Delete on data1  (cost=0.00..17906.00 rows=1 width=6) (actual
time=56.817..56.818 rows=0 loops=1)
   WAL: records=2 fpi=1 bytes=8237
   ->  Seq Scan on data1  (cost=0.00..17906.00 rows=1 width=6) (actual
time=1.269..56.800 rows=1 loops=1)
         Filter: (c1 = 20001)
         Rows Removed by Filter: 999998
         WAL: records=1 fpi=1 bytes=8183
 Planning Time: 0.085 ms
 Execution Time: 56.843 ms
(8 rows)

postgres=> CREATE TABLE gen1(c1 INT GENERATED ALWAYS AS IDENTITY,
c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> INSERT INTO gen1 OVERRIDING USER VALUE VALUES
(100, 'data1') ;
INSERT 0 1
postgres=> SELECT * FROM gen1 ;
 c1 |  c2
----+-------
  1 | data1
(1 row)

postgres=> SELECT
jsonb_path_query('"27-09-2020"', '$.datetime("dd-mm-yyyy")') ;
 jsonb_path_query
------------------
 "2020-09-27"
(1 row)

postgres=# SELECT MIN(flush_lsn), MAX(flush_lsn)
FROM pg_stat_replication ;
    min    |    max
-----------+-----------
 0/94FD1E0 | 0/94FD1E0
(1 row)

postgres=> SELECT (ROW(2, 3.1)).f1, (ROW(4, 5.1)).f2 ;
f1 | f2
----+-----
  2 | 5.1
(1 row)

postgres=> SELECT * FROM data1 WHERE c1 < 4 ORDER BY c1
FETCH FIRST 2 ROWS ONLY ;
 c1 |  c2
----+-------
  1 | data1
  1 | data1
(2 rows)
postgres=> SELECT * FROM data1 WHERE c1 < 4 ORDER BY c1
FETCH FIRST 2 ROWS WITH TIES ;
 c1 |  c2
----+-------
  1 | data1
  1 | data1
  1 | data1
(3 rows)

postgres=> VACUUM (PARALLEL 4, VERBOSE) data1 ;
INFO:  vacuuming "public.data1"
INFO:  launched 2 parallel vacuum workers for index vacuuming (planned: 2)
INFO:  scanned index "idx3_data1" to remove 1000 row versions by parallel
vacuum worker
DETAIL:  CPU: user: 0.39 s, system: 0.11 s, elapsed: 12.45 s
INFO:  scanned index "idx2_data1" to remove 1000 row versions by parallel
vacuum worker
DETAIL:  CPU: user: 0.39 s, system: 0.12 s, elapsed: 12.56 s
INFO:  scanned index "idx1_data1" to remove 1000 row versions
DETAIL:  CPU: user: 0.47 s, system: 0.17 s, elapsed: 53.26 s
INFO:  "data1": removed 1000 row versions in 1000 pages
…

postgres=> CREATE TABLE point_tbl(f1 point) ;
CREATE TABLE
postgres=> CREATE TABLE box_tbl(f1 box) ;
CREATE TABLE
postgres=> SELECT p.f1, b.f1, p.f1 <-> b.f1 AS dist_pb,
b.f1 <-> p.f1 AS dist_bp FROM point_tbl p, box_tbl b ;
f1 | f1 | dist_pb | dist_bp
----+----+---------+---------
(0 rows)

postgres=> SELECT gen_random_uuid() ;
           gen_random_uuid
--------------------------------------
 3759712a-e7f6-4348-b62c-85535c831e7e
(1 row)

postgres=> SELECT TO_CHAR(current_timestamp, 'HH:MI:SS.FF6') ;
     to_char
-----------------
 01:39:56.077670
(1 row)

postgres=> SELECT trim_scale(1.234000::numeric),
min_scale(1.234000::numeric) ;
 trim_scale | min_scale
------------+-----------
      1.234 |         3
(1 row)

postgres=> SELECT
jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]',
'{0,f1}', null) ;
             jsonb_set_lax
----------------------------------------
 [{"f1": null, "f2": null}, 2, null, 3]
(1 row)

postgres=> SELECT gcd(1071, 462), lcm(1071, 46) ;
 gcd |  lcm
-----+-------
  21 | 49266
(1 row)

postgres=> SELECT '㋿' IS NFKD NORMALIZED ;
 is_normalized
---------------
 f
(1 row)
postgres=> SELECT normalize('㋿', NFKD) ;
 normalize
-----------
 令和
(1 row)

postgres=# SELECT pg_stat_reset_slru('subtrans') ;
pg_stat_reset_slru
--------------------
(1 row)

postgres=# SET backtrace_functions = 'pg_strtoint32' ;
SET
postgres=# SELECT int 'foobar' ;
ERROR:  invalid input syntax for type integer: "foobar"
LINE 1: SELECT int 'foobar' ;
postgres=# \q
$ tail data/log/postgresql.log
2020-09-24 18:36:42.452 JST [2532] BACKTRACE:
        postgres: postgres postgres [local] SELECT() [0x807d2e]
        postgres: postgres postgres [local] SELECT(int4in+0xd) [0x7d27ad]
        postgres: postgres postgres [local] SELECT(InputFunctionCall+0x67)
[0x884d27]
        postgres: postgres postgres [local] SELECT(OidInputFunctionCall+0x31)
[0x884f31]
        postgres:
postgres
postgres
[local]
SELECT(coerce_type+0x361)
[0x5785f1]
        postgres: postgres postgres [local] SELECT(coerce_to_target_type+0x9b)
[0x577d8b]
…
        postgres:
postgres
postgres
[local]
SELECT(parse_analyze+0x4f)
[0x55874f]
        postgres:
postgres
postgres
[local]
SELECT(pg_analyze_and_rewrite+0x13) [0x76ffa3]
        postgres: postgres postgres [local] SELECT() [0x770483]
        postgres:
postgres
postgres
[local]
SELECT(PostgresMain+0x1037)
[0x7718f7]
        postgres: postgres postgres [local] SELECT() [0x483e46]
        postgres:
postgres
postgres
[local]
SELECT(PostmasterMain+0xe88)
[0x6ff668]
        postgres: postgres postgres [local] SELECT(main+0x46a) [0x484a9a]
        /lib64/libc.so.6(__libc_start_main+0xf5) [0x7f19383693d5]
        postgres: postgres postgres [local] SELECT() [0x484b01]
2020-09-24 18:36:42.452 JST [2532] STATEMENT:  SELECT int 'foobar' ;

$ dropdb --force --echo demodb
SELECT pg_catalog.set_config('search_path', '', false);
DROP DATABASE demodb WITH (FORCE);

$ pg_basebackup --verbose --no-estimate-size -D back
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 1/44000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_3822"
pg_basebackup: write-ahead log end point: 1/44000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

$ pg_dump -d demodb --include-foreign-data=svr1
--
-- PostgreSQL database dump
…
CREATE FOREIGN TABLE public.foreign1 (
    c1 numeric,
    c2 character varying(10)
)
SERVER svr1;
…
--
-- Data for Name: data1; Type: TABLE DATA; Schema: public; Owner:
demo
--
COPY public.foreign1 (c1, c2) FROM stdin;
100     data1
\.
…

$ pg_dump -d demodb | grep EXTENSION
-- Name: cube; Type: EXTENSION; Schema: -; Owner: -
CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA public;
-- Name: EXTENSION cube; Type: COMMENT; Schema: -; Owner:
COMMENT ON EXTENSION cube IS 'data type for multidimensional
cubes';
ALTER FUNCTION public.func1() DEPENDS ON EXTENSION cube;
$

$ pg_rewind --help
pg_rewind resynchronizes a PostgreSQL cluster with another copy of the
cluster.
Usage:
  pg_rewind [OPTION]...
Options:
  -c, --restore-target-wal       use restore_command in target
configuration to
                                 retrieve WAL files from archives
  -D, --target-pgdata=DIRECTORY  existing data directory to modify
      --source-pgdata=DIRECTORY  source data directory to synchronize with
      --source-server=CONNSTR    source server to synchronize with
  -n, --dry-run                  stop before modifying anything
  -N, --no-sync                  do not wait for changes to be written
                                 safely to disk
  -P, --progress                 write progress messages
  -R, --write-recovery-conf      write configuration for replication
                                 (requires --source-server)
      --debug                    write a lot of debug messages
      --no-ensure-shutdown       do not automatically fix unclean shutdown
  -V, --version                  output version information, then exit
  -?, --help                     show this help, then exit
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

$ pg_basebackup -D back
$ pg_verifybackup back
backup successfully verified
$ echo $?
0

$ pg_basebackup -D back
$ vi back/postgresql.conf
$ vi back/pg_hba.conf
$ pg_verifybackup back
pg_verifybackup: error: "pg_hba.conf" has size 4761 on disk but
size 4760 in the manifest
pg_verifybackup:
error:
checksum
mismatch
for
file
"postgresql.conf"
$

$ pg_basebackup -D back --no-manifest
$ pg_verifybackup back
pg_verifybackup:
fatal:
could
not
open
file
"back/backup_manifest": No such file or directory
$ rm -r back
$ pg_basebackup -D back -Ft
$ pg_verifybackup back
pg_verifybackup: error: "base.tar" is present on disk but not in
the manifest
pg_verifybackup: error: "pg_wal.tar" is present on disk but not in
the manifest
pg_verifybackup: error: "base/13577/3433" is present in the
manifest but not on disk
…
Try "pg_waldump --help" for more information.
pg_verifybackup: error: WAL parsing failed for timeline 1
$

postgres=> \dAc
                                List of operator classes
   AM   |         Input type          | Storage type |     Operator class     | Default?
--------+-----------------------------+--------------+------------------------+------
 brin   | "char"                      |              | char_minmax_ops        | yes
 brin   | anyrange                    |              | range_inclusion_ops    | yes
 brin   | bigint                      |              | int8_minmax_ops        | yes
 brin   | bit                         |              | bit_minmax_ops         | yes
 brin   | bit varying                 |              | varbit_minmax_ops      | yes
 brin   | box                         |              | box_inclusion_ops      | yes
 brin   | bytea                       |              | bytea_minmax_ops       | yes
 brin   | character                   |              | bpchar_minmax_ops      | yes
 brin   | date                        |              | date_minmax_ops        | yes
 brin   | double precision            |              | float8_minmax_ops      | yes
…

postgres=> SELECT * FROM data1 WHERE c1=10 ;
 c1 |  c2
----+-------
 10 | data1
 10 | data1
10 | data1
(3 rows)
postgres=> \g (format=csv)
c1,c2
10,data1
10,data1
10,data1

postgres=> \warn `date`
Thu Sep 24 21:10:09 JST 2020

postgres=> \e
postgres=> SELECT * FROM data1
postgres->

postgres=> \d+
                                     List of relations
  Schema   |        Name        | Type  |  Owner   | Persistence |    Size    | Description
-----------+--------------------+-------+----------+-------------+------------+------
 pg_temp_3 | temp1              | table | demo     | temporary   | 8192 bytes |
 public    | data1              | table | demo     | permanent   | 42 MB      |
public    | unlogged1          | table | demo     | unlogged    | 16 kB      |
(3 rows)

postgres=> \set PROMPT2 '>%w'
postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10))
>           TABLESPACE pg_default ;
CREATE TABLE

$ reindexdb --concurrently --jobs 2 postgres
reindexdb: warning: cannot reindex system catalogs concurrently,
skipping all

$ vacuumdb --parallel=4 postgres
vacuumdb: vacuuming database "postgres"

$ pg_controldata --help
pg_controldata displays control information of a PostgreSQL database cluster.
Usage:
  pg_controldata [OPTION] [DATADIR]
Options:
 [-D, --pgdata=]DATADIR  data directory
  -V, --version          output version information, then exit
  -?, --help             show this help, then exit
If no data directory (DATADIR) is specified, the environment variable PGDATA
is used.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

postgres=# CREATE EXTENSION adminpack ;
CREATE EXTENSION
postgres=# SELECT
pg_file_sync('postgresql-2020-09-24_120622.log') ;
 pg_file_sync
--------------
(1 row)

postgres=# LOAD 'auto_explain' ;
LOAD
postgres=# SET auto_explain.log_wal = on ;
SET
postgres=# SET auto_explain.log_analyze = on ;
SET
postgres=# SET auto_explain.log_min_duration = 0 ;
SET
postgres=# DELETE FROM data1 WHERE c1=100 ;
DELETE 1
postgres=# \! tail -7 log/postgres.log
2020-09-24 21:16:28.707 JST [4731] LOG:  duration: 0.064 ms  plan:
        Query Text: DELETE FROM data1 WHERE c1=100 ;
        Delete on data1  (cost=0.00..4.50 rows=2 width=6) (actual
time=0.063..0.063 rows=0 loops=1)
          WAL: records=2 fpi=2 bytes=8966
          ->  Seq Scan on data1  (cost=0.00..4.50 rows=2 width=6) (actual
time=0.018..0.026 rows=2 loops=1)
                Filter: (c1 = '100'::numeric)
                Rows Removed by Filter: 198

postgres=# CREATE EXTENSION dict_int ;
CREATE EXTENSION
postgres=# ALTER TEXT SEARCH DICTIONARY intdict (ABSVAL = true) ;
ALTER TEXT SEARCH DICTIONARY
postgres=# \dFd+ intdict
                            List of text search dictionaries
 Schema |  Name   |        Template         |  Init options   |       Description
--------+---------+-------------------------+-----------------+-------------

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=806eb92c018102da9514443ad19160

 public | intdict | public.intdict_template | absval = 'true' | dictionary

c7b1841062

for integers
(1 row)

postgres=> SELECT typreceive, typsend FROM pg_type WHERE typname='ltree' ;
-[ RECORD 1 ]----------
typreceive | ltree_recv
typsend    | ltree_send

postgres=# SELECT t_infomask, t_infomask2, raw_flags, combined_flags
               FROM heap_page_items(get_raw_page('data1', 0)),
               LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2)
               m(raw_flags, combined_flags) ;
-[ RECORD 1 ]--+---------------------------------------------------------
t_infomask     | 2306
t_infomask2    | 2
raw_flags      | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}
combined_flags | {}

postgres=# SELECT * FROM bt_metap('idx1_data1') ;
-[ RECORD 1 ]-----------+-------
magic                   | 340322
version                 | 4
root                    | 3
level                   | 1
fastroot                | 3
fastlevel               | 1
oldest_xact             | 0
last_cleanup_num_tuples | -1
allequalimage           | t

postgres=# SELECT * FROM bt_page_items('idx1_data1', 1) ;
-[ RECORD 1 ]-----------------------
itemoffset | 1
ctid       | (0,1)
itemlen    | 16
nulls      | f
vars       | t
data       | 0b 00 80 01 00 00 00 00
dead       | f
htid       | (0,101)
tids       |

postgres=# SELECT queryid, plans, total_plan_time, min_plan_time
FROM pg_stat_statements WHERE plans != 0;
       queryid        | plans |   total_plan_time    | min_plan_time
----------------------+-------+----------------------+---------------
  1809546128015582813 |     1 |               0.9533 |        0.9533
  1580010417825738145 |   900 |   29.697499999999998 |        0.0194
   726988806503882440 |     1 |               0.0889 |        0.0889
  6547867665616429211 |     1 |               0.3364 |        0.3364
  1518237015596383425 |     1 |               0.0522 |        0.0522
  6090531631178874043 |   900 |    51.09360000000001 |        0.0221
 -8944905077122340531 |     6 |  0.22340000000000002 |        0.0333
  -261743227044297956 |    10 | 0.061900000000000004 |        0.0054
…

postgres=# CREATE USER MAPPING FOR demo SERVER remhost1
OPTIONS (user 'demo') ;
CREATE USER MAPPING

postgres=> CREATE FOREIGN TABLE data1(c1 INT, c2 VARCHAR(10))
SERVER remhost1 ;
CREATE FOREIGN TABLE
postgres=> SELECT * FROM data1 ;
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the user
mapping.

By setting the option password_required to 'false', password setting for user mapping can be avoided.

postgres=# ALTER USER MAPPING FOR demo SERVER remhost1 OPTIONS
(password_required 'false');
ALTER USER MAPPING

postgres=> CREATE EXTENSION plperl ;
CREATE EXTENSION
postgres=> CREATE EXTENSION bool_plperl ;
CREATE EXTENSION
postgres=> CREATE FUNCTION perl_and(bool, bool) RETURNS bool
TRANSFORM FOR TYPE bool
AS $$
   my ($a, $b) = @_;
return $a && $b;
$$ LANGUAGE plperl ;
CREATE FUNCTION