postgres=# ALTER SYSTEM SET max_connections = 1000 ;
ALTER SYSTEM
postgres=# SELECT sourcefile, name, setting FROM pg_file_settings  WHERE
name = 'max_connections' ;
            sourcefile                |      name       | setting
------------------------------------------+-----------------+---------
 /usr/local/pgsql/data/postgresql.auto.conf | max_connections | 1000
(1 row)

postgres=> SELECT r.client_hostname, r.pid, s.slot_name FROM
pg_stat_replication r
LEFT OUTER JOIN pg_replication_slots s ON r.pid = s.active_pid ;
 client_hostname |  pid  | slot_name
-----------------+-------+-----------
 dbslv1          | 12915 | slot_1
 dbslv2          | 12934 |
(2 rows)

$ pg_ctl -D data.slave1 promote
server promoting
$
$ psql -c 'SELECT pg_switch_xlog()'
 pg_switch_xlog
----------------
 0/146D2FA8
(1 row)
$ ls -1 arch.slave1
000000010000000000000014.partial
000000020000000000000014
00000002.history
$

$ pg_rewind --source-server='host=remhost1 port=5432 user=postgres'
 --target-pgdata=data.p -P
connected to remote server
target server need to use either data checksums or "wal_log_hints = on"
Failure, exiting
The instance of the target database cluster must be stopped normally.

$ pg_rewind --source-server='host=remhost1 port=5432 user=postgres'
 --target-pgdata=data.p -P
target server must be shut down cleanly
Failure, exiting
Data copy procedure, as well as pg_basebackup command, use the connection to the wal sender
process. It is necessary to set the max_wal_senders parameter and pg_hba.conf file of connection
destination (data provider) instance.

$ pg_rewind --source-server='host=remhost1 port=5432 user=postgres'
 --target-pgdata=data.p
connected to server
The servers diverged at WAL position 0/9000060 on timeline 1.
Rewinding from last common checkpoint at 0/8000060 on timeline 1
reading source file list
reading target file list
reading WAL in target
need to copy 53 MB (total source directory size is 76 MB)
54294/54294 kB (100%) copied
creating backup label and updating control file
Done!
4. Create the recovery.conf
Pg_rewind command does not create recovery.conf file in the target database cluster. Therefore
you should create recovery.conf file in the new slave instance.
5. Modify the postgresql.conf
By the execution of pg_rewind command, postgresql.conf file has been overwritten and copied
from the remote host. Edit the parameters if necessary.
6. Start the new slave instance
Start the new slave instance. And check the pg_stat_replication catalog on the new master
instance.

$ vacuumdb --jobs=-1
vacuumdb: number of parallel "jobs" must be at least 1
$ vacuumdb --jobs=1025
vacuumdb: too many parallel jobs requested (maximum: 1023)

$ vacuumdb --jobs=10 -d demodb &
vacuumdb: vacuuming database "demodb"
$ ps -ef | grep postgres
postgres 14539     1  0 10:59 pts/2  00:00:00 /usr/local/pgsql/bin/postgres -D data
postgres 14540 14539  0 10:59 ?      00:00:00 postgres: logger process
postgres 14542 14539  0 10:59 ?      00:00:00 postgres: checkpointer process
postgres 14543 14539  0 10:59 ?      00:00:00 postgres: writer process
postgres 14544 14539  0 10:59 ?      00:00:00 postgres: wal writer process
postgres 14545 14539  0 10:59 ?      00:00:00 postgres: stats collector process
postgres 14569 14539  6 11:00 ?      00:00:00 postgres: postgres demodb [local] VACUUM
postgres 14570 14539  0 11:00 ?      00:00:00 postgres: postgres demodb [local] idle
postgres 14571 14539  5 11:00 ?      00:00:00 postgres: postgres demodb [local] VACUUM
postgres 14572 14539  7 11:00 ?      00:00:00 postgres: postgres demodb [local] VACUUM
postgres 14573 14539  0 11:00 ?      00:00:00 postgres: postgres demodb [local] idle
postgres 14574 14539  0 11:00 ?      00:00:00 postgres: postgres demodb [local] idle
postgres 14575 14539  9 11:00 ?      00:00:00 postgres: postgres demodb [local] VACUUM
postgres 14576 14539  5 11:00        00:00:00 postgres: postgres demodb [local] VACUUM
postgres 14577 14539  0 11:00 ?      00:00:00 postgres: postgres demodb [local] idle
postgres 14578 14539  1 11:00 ?      00:00:00 postgres: postgres demodb [local] idle
It the value specified in the --jobs parameter is larger than the number of --table parameters, the upper
limit of the degree of parallelism is the number of tables. In addition, since the PostgreSQL
max_connections parameter is not considered in the calculation of the number of sessions, in case that
the excess of the number of sessions is detected, "FATAL: sorry, too many clients already" error will
occur.

postgres=> \pset pager_min_lines 20
Pager won't be used for less than 20 lines

postgres=> SELECT * FROM notexists1 ;
ERROR:  relation "notexists1" does not exist
LINE 1: SELECT * FROM notexists1;
                      ^
postgres=> \set ECHO errors
postgres=> SELECT * FROM notexists1 ;
ERROR:  relation "notexists1" does not exist
LINE 1: SELECT * FROM notexists1;
                      ^
STATEMENT:  SELECT * FROM notexists1 ;

postgres=> CREATE TABLE badschema.table1(c1 NUMERIC, c2 VARCHAR(10)) ;
ERROR:  schema "badschema" does not exist
LINE 1: CREATE TABLE badschema.table1(c1 NUMERIC, c2 VARCHAR(10)) ;

postgres=> \timing
Timing is on.
demodb=> \watch 1
Watch every 1s  Fri Aug  7 11:56:58 2015
              now
-------------------------------
 2015-08-07 11:56:59.931996+09
(1 row)
Time: 0.458 ms

$ pgbench -c 10 -d pgbench -U pgbench -L 10
pghost:  pgport:  nclients: 10 nxacts: 10 dbName: pgbench
starting vacuum...end.
……………………………………………..
number of transactions actually processed: 100/100
number of transactions above the 10.0 ms latency limit: 64 (64.000 %)
latency average: 17.065 ms
latency stddev: 12.816 ms
tps = 479.927051 (including connections establishing)
tps = 546.603406 (excluding connections establishing)

$ pgbench -f bench.sql -U user1 postgres
starting vacuum...ERROR:  relation "pgbench_branches" does not exist
(ignoring this error and continuing anyway)
ERROR:  relation "pgbench_tellers" does not exist
(ignoring this error and continuing anyway)
ERROR:  relation "pgbench_history" does not exist
(ignoring this error and continuing anyway)
end.
transaction type: Custom query
…………………………………………………………
Some of the new features other than the above has been implemented to pgbench command.

$ reindexdb -v demodb
INFO:  index "pg_class_oid_index" was reindexed
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_class_relname_nsp_index" was reindexed
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_class_tblspc_relfilenode_index" was reindexed
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
………………………………..

postgres=> ALTER TABLE vacuum1 SET (log_autovacuum_min_duration = 1000) ;
ALTER TABLE
postgres=> \d+ vacuum1
                               Table "public.vacuum1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+--------
 c1     | numeric               |         | main     |         |
 c2     | character varying(10)     |         | extended |          |
Options: log_autovacuum_min_duration=1000

$ cat data/recovery.conf
restore_command = 'cp /usr/local/pgsql/arch/%f %p'
pause_at_recovery_target = on
$
$ pg_ctl -D data -w start
waiting for server to start....LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
.... stopped waiting
pg_ctl: could not start server
Examine the log output
$
$ cat data/pg_log/postgresql-2015-08-07_111751.log
LOG:  database system was shut down at 2015-08-07 11:17:20 JST
FATAL:  unrecognized recovery parameter "pause_at_recovery_target"
LOG:  startup process (PID 12233) exited with exit code 1
LOG:  aborting startup due to startup process failure

postgres=> CREATE TABLE upsert1 (key NUMERIC, val VARCHAR(10)) ;
CREATE TABLE
postgres=> ALTER TABLE upsert1 ADD CONSTRAINT pk_upsert1 PRIMARY KEY (key) ;
ALTER TABLE
postgres=> INSERT INTO upsert1 VALUES (100, 'Val 1') ;
INSERT 0 1
postgres=> INSERT INTO upsert1 VALUES (200, 'Val 2') ;
INSERT 0 1
postgres=> INSERT INTO upsert1 VALUES (300, 'Val 3') ;
INSERT 0 1
The following is a description example of the ON CONFLICT clause. Since DO NOTHING is
specified in the handling part, nothing is done even if constraint violation occurs.

postgres=> INSERT INTO upsert1 VALUES (200, 'Update 1')
ON CONFLICT DO NOTHING ; -- omit constraint name or column
INSERT 0 0
postgres=> INSERT INTO upsert1 VALUES (200, 'Update 1')
ON CONFLICT(key) DO NOTHING ; -- set column name
INSERT 0 0
postgres=> INSERT INTO upsert1 VALUES (200, 'Update 1')
ON CONFLICT(val) DO NOTHING ; -- error when no constraint column set
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
postgres=> INSERT INTO upsert1 VALUES (200, 'Update 1')
ON CONFLICT ON CONSTRAINT pk_upsert1 DO NOTHING ; -- set constraint
INSERT 0 0
In the DO UPDATE clause, you describes the update process. This is basically the same as the part
subsequent the SET clause in the UPDATE statement. If you use an alias EXCLUDED, you can
access the records that could not be stored when INSERT statement is executed.

postgres=> INSERT INTO upsert1 VALUES (400, 'Upd4')
ON CONFLICT DO UPDATE SET val = EXCLUDED.val ; -- No constraint error
ERROR:  ON CONFLICT DO UPDATE requires inference specification or constraint name
LINE 2: ON CONFLICT DO UPDATE SET val = EXCLUDED.val;
        ^
HINT:  For example, ON CONFLICT ON CONFLICT (<column>).
postgres=> INSERT INTO upsert1 VALUES (300, 'Upd3')
ON CONFLICT(key) DO UPDATE SET val = EXCLUDED.val ; -- Use EXCLUDED alias
INSERT 0 1
postgres=> INSERT INTO upsert1 VALUES (300, 'Upd3')
ON CONFLICT(key) DO UPDATE SET val = EXCLUDED.val WHERE upsert1.key = 100 ;
INSERT 0 0 -- Can determin UPDATE conditions when specify the WHERE clause

postgres=> EXPLAIN INSERT INTO upsert1 VALUES (200, 'Update 1')
ON CONFLICT(key) DO NOTHING ;
                     QUERY PLAN
-----------------------------------------------------
 Insert on upsert1  (cost=0.00..0.01 rows=1 width=0)
   Conflict Resolution: NOTHING
   Conflict Arbiter Indexes: pk_upsert1
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(4 rows)
postgres=> EXPLAIN INSERT INTO upsert1 VALUES (400, 'Upd4')
ON CONFLICT(key) DO UPDATE SET val = EXCLUDED.val ;
                     QUERY PLAN
-----------------------------------------------------
 Insert on upsert1  (cost=0.00..0.01 rows=1 width=0)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: pk_upsert1
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(4 rows)
postgres=> EXPLAIN INSERT INTO upsert1 VALUES (400, 'Upd4')
ON CONFLICT(key) DO UPDATE SET val = EXCLUDED.val WHERE upsert1.key = 100 ;
                     QUERY PLAN
-----------------------------------------------------
 Insert on upsert1  (cost=0.00..0.01 rows=1 width=0)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: pk_upsert1
   Conflict Filter: (upsert1.key = '100'::numeric)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(5 rows)
Current version does not support ON CONFLICT DO UPDATE statement to the remote instance
using postgres_fdw module.

postgres=> CREATE TABLE main1 (key1 NUMERIC, val1 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE TABLE main1_part100 (CHECK(key1 < 100)) INHERITS (main1) ;
CREATE TABLE
postgres=> CREATE TABLE main1_part200 (CHECK(key1 >= 100 AND key1 < 200))
INHERITS (main1) ;
CREATE TABLE
postgres=> ALTER TABLE main1_part100 ADD CONSTRAINT pk_main1_part100
PRIMARY KEY (key1);
ALTER TABLE
postgres=> ALTER TABLE main1_part200 ADD CONSTRAINT pk_main1_part200
PRIMARY KEY (key1);
ALTER TABLE
postgres=> CREATE OR REPLACE FUNCTION func_main1_insert()
RETURNS TRIGGER AS $$
 BEGIN
   IF    (NEW.key1 < 100) THEN
         INSERT INTO main1_part100 VALUES (NEW.*) ;
   ELSIF (NEW.key1 >= 100 AND NEW.key1 < 200) THEN
         INSERT INTO main1_part200 VALUES (NEW.*) ;
   ELSE
          RAISE EXCEPTION 'ERROR! key1 out of range.' ;
   END IF ;
   RETURN NULL ;
 END ;
$$  LANGUAGE 'plpgsql';
CREATE FUNCTION

postgres=> CREATE TRIGGER trg_main1_insert BEFORE INSERT ON main1
     FOR EACH ROW EXECUTE PROCEDURE func_main1_insert() ;
CREATE TRIGGER
postgres=> INSERT INTO main1 VALUES (100, 'DATA100') ;
INSERT 0 0
postgres=> INSERT INTO main1 VALUES (100, 'DATA100') ;
ERROR:  duplicate key value violates unique constraint "pk_main1_part200"
DETAIL:  Key (key1)=(100) already exists.
CONTEXT:  SQL statement "INSERT INTO main1_part200 VALUES (NEW.*)"
PL/pgSQL function func_main1_insert() line 6 at SQL statement
postgres=> INSERT INTO main1 VALUES (100, 'DATA100')
ON CONFLICT DO NOTHING ;
ERROR:  duplicate key value violates unique constraint "pk_main1_part200"
DETAIL:  Key (key1)=(100) already exists.
CONTEXT:  SQL statement "INSERT INTO main1_part200 VALUES (NEW.*)"
PL/pgSQL function func_main1_insert() line 6 at SQL statement

postgres=> CREATE TABLE logtbl1 (c1 NUMERIC, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> \d+ logtbl1
                               Table "public.logtbl1"
 Column |         Type      | Modifiers  | Storage  | Stats target    | Description
--------+-----------------------+-----------+----------+--------------+------------
 c1     | numeric           |           | main     |             |
 c2     | character varying(10) |           | extended  |             |
postgres=> ALTER TABLE logtbl1 SET UNLOGGED ;
ALTER TABLE
postgres=> \d+ logtbl1
                          Unlogged table "public.logtbl1"
 Column |         Type      | Modifiers  | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+------------
 c1     | numeric           |           | main     |         |
 c2     | character varying(10) |           | extended  |         |
Using the \d+ command, you can see that the normal table is changed to UNLOGGED table.

postgres=> SELECT relname, relfilenode, relpersistence FROM pg_class WHERE
relname='logtbl1' ;
 relname | relfilenode | relpersistence
---------+-------------+----------------
 logtbl1 |     16483 | p
(1 row)
postgres=> ALTER TABLE logtbl1 SET UNLOGGED ;
ALTER TABLE
postgres=> SELECT relname, relfilenode, relpersistence FROM pg_class WHERE
relname='logtbl1' ;
 relname | relfilenode | relpersistence
---------+-------------+----------------
 logtbl1 |     16489 | u
(1 row)

postgres=# CREATE EXTENSION postgres_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER remsvr1 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.1.200', port '5432', dbname 'demodb') ;
CREATE SERVER
postgres=# CREATE USER MAPPING FOR public SERVER remsvr1
OPTIONS (user 'user1', password 'secret') ;
CREATE USER MAPPING
postgres=# CREATE SCHEMA schema2 ;
CREATE SCHEMA
postgres=# IMPORT FOREIGN SCHEMA schema1 FROM SERVER remsvr1 INTO schema2 ;
IMPORT FOREIGN SCHEMA

postgres=# IMPORT FOREIGN SCHEMA public FROM SERVER remsvr1 INTO schemax ;
ERROR:  schema "schemax" does not exist  -- No Local Schema
postgres=# IMPORT FOREIGN SCHEMA schema1 FROM SERVER remsvr1 INTO schema1 ;
ERROR:  relation "data1" already exists  -- Exists same name table
CONTEXT:  importing foreign table "table2"
postgres=> IMPORT FOREIGN SCHEMA public FROM SERVER remsvr1 INTO schema1 ;
ERROR:  password is required
DETAIL:  Non-superuser cannot connect if the server does not request a password.
HINT:  Target server's authentication method must be changed.

(remote) postgres=> CREATE SEQUENCE seq1 ;
CREATE SEQUENCE
(remote)postgres=> CREATE TABLE data1(c1 NUMERIC DEFAULT nextval('seq1')) ;
CREATE TABLE
(local) postgres=# IMPORT FOREIGN SCHEMA public FROM SERVER remsvr2 INTO schema1
OPTIONS (import_default 'true') ;
ERROR:  relation "public.seq1" does not exist
CONTEXT:  importing foreign table "data1"

postgres=> CREATE TABLE lock1 (key NUMERIC, val TEXT) ;
CREATE TABLE
postgres=> INSERT INTO lock1 VALUES (generate_series(1, 2000), 'initial') ;
INSERT 0 2000
postgres=> BEGIN ;
BEGIN
postgres=> SELECT * FROM lock1 WHERE key = 1000 FOR SHARE ;
key  |   val
------+---------
 1000 | initial
(1 row)
The above example create a table and locked a tuple of the "key = 1000" with the FOR SHARE
clause. In the following example, we performed a search using SKIP LOCKED clause in the other
session. Since tuple of the "key = 1000" conflicts with SELECT statement specifying the FOR
UPDATE clause, it becomes wait state under the standard condition, or it causes an error in case that
NOWAIT is specified. By specifying the SKIP LOCKED clause, tuples other than "key = 1000" has

postgres=> SELECT * FROM lock1 WHERE key IN (999, 1000, 1001) FOR UPDATE SKIP LOCKED ;
 key  |   val
------+---------
  999 | initial
 1001 | initial
(2 rows)

postgres=> CREATE TABLE parent1(key NUMERIC, val TEXT) ;
CREATE TABLE
Create a table (inherit1, inherit2, inherit3) on the remote instance.

postgres=> CREATE TABLE inherit1(key NUMERIC, val TEXT) ;
CREATE TABLE
Execute the CREATE FOREIGN TABLE statement to create an external table to the table (inherit1,
inherit2, inherit3) on each remote instance.

postgres=# CREATE EXTENSION postgres_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER remsvr1 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remsvr1', dbname 'demodb', port '5432') ;
CREATE SERVER
postgres=# CREATE USER MAPPING FOR public SERVER remsvr1
OPTIONS (user 'demo', password 'secret') ;
CREATE USER MAPPING
postgres=# GRANT ALL ON FOREIGN SERVER remsvr1 TO public ;
GRANT
postgres=> CREATE FOREIGN TABLE inherit1(CHECK(key < 1000))
INHERITS (parent1) SERVER remsvr1 ;
CREATE FOREIGN TABLE
The changes from the previous versions are: CREATE FOREIGN TABLE statement specified
CHECK constraint rather than column definition, and INHERITS clause specified the original table.
The above is an example of only one instance, but in fact, you should execute CREATE SERVER
statement, CREATE USER MAPPING statement, and CREATE FOREIGN TABLE statement for
multiple instances.

postgres=> \d+ inherit2
                          Foreign table "public.inherit2"
 Column |  Type   | Modifiers | FDW Options | Storage  | Stats target | Description
--------+---------+-----------+-------------+----------+--------------+------------
 key    | numeric |          |           | main     |         |
 val    | text    |           |          | extended  |         |
Check constraints:
    "inherit2_key_check" CHECK (key >= 1000::numeric AND key < 2000::numeric)
Server: remsvr2
Inherits: parent1
When you check the execution plan, you can see that SQL access only to a specific instance due to
CHECK constraints.

postgres=> EXPLAIN SELECT * FROM parent1 WHERE key = 1500 ;
                      QUERY PLAN
-----------------------------------------------------------------------
 Append  (cost=0.00..121.72 rows=6 width=64)
   ->  Seq Scan on parent1  (cost=0.00..0.00 rows=1 width=64)
         Filter: (key = '1500'::numeric)
   ->  Foreign Scan on inherit2  (cost=100.00..121.72 rows=5 width=64)
(4 rows)

postgres=> ALTER USER CURRENT_USER PASSWORD 'secret' ;
ALTER ROLE

postgres=> CREATE TABLE upd1(c1 NUMERIC, c2 NUMERIC, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE TABLE upd2(c1 NUMERIC, c2 NUMERIC, c3 VARCHAR(10)) ;
CREATE TABLE
This will update the value of c2, c3 column of the table upd2 with c2, c3 column of the table upd1.
At that time, the tuple which has the same value in c1 column is used.

postgres=> UPDATE upd2 SET c2 = upd1.c2, c3 = upd1.c3
FROM (SELECT * FROM upd1) AS upd1
WHERE upd1.c1 = upd2.c1 ;
UPDATE 2

postgres=> UPDATE upd2 SET (c2, c3) =
(SELECT c2, c3 FROM upd1 WHERE upd1.c1 = upd2.c1) ;
UPDATE 2

postgres=> EXPLAIN ANALYZE SELECT COUNT(*) FROM data1 TABLESAMPLE SYSTEM (10) ;
                           QUERY PLAN
-------------------------------------------------------------------------------
Aggregate  (cost=341.00..341.01 rows=1 width=0) (actual time=4.914..4.915 rows=1 loops=1)
->
Sample Scan (system) on data1
(cost=0.00..316.00 rows=10000 width=0)
(actualtime=0.019..3.205 rows=10090 loops=1)
 Planning time: 0.106 ms
 Execution time: 4.977 ms
(4 rows)

postgres=> EXPLAIN ANALYZE SELECT COUNT(*) FROM data1 TABLESAMPLE
BERNOULLI (10) ;
                          QUERY PLAN
-------------------------------------------------------------------------------
 Aggregate  (cost=666.00..666.01 rows=1 width=0) (actual time=13.654..13.655 rows=1 loops=1)
->  Sample Scan (bernoulli) on data1  (cost=0.00..641.00 rows=10000 width=0) (actual
time=0.013..12.121 rows=10003 loops=1)
 Planning time: 0.195 ms
 Execution time: 13.730 ms
(4 rows)

postgres=> REINDEX SCHEMA schema1 ;
REINDEX
postgres=> REINDEX SCHEMA schema2 ;
ERROR:  must be owner of schema schema2
postgres=> BEGIN ;
BEGIN
postgres=> REINDEX SCHEMA schema1 ;
ERROR:  REINDEX SCHEMA cannot run inside a transaction block
postgres=>
To execute REINDEX SCHEMA statement, the following conditions are required.

postgres=> REINDEX (VERBOSE) TABLE data1 ;
INFO:  index "idx1_data1" was reindexed
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  index "pg_toast_16424_index" was reindexed
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
REINDEX

postgres=> SELECT c1, SUM(c2) FROM role1 GROUP BY ROLLUP (c1) ;
  c1  |  sum
------+-------
 val1 |  5050
 val3 |  5050
 val4 |  5050
     | 15150
(4 rows)

postgres=> CREATE FOREIGN TABLE data1 (c1 NUMERIC, c2 VARCHAR(10),
CHECK(c1 > 0)) SERVER remsvr1 ;
CREATE FOREIGN TABLE
postgres=> INSERT INTO data1 VALUES (-2, 'add') ;
INSERT 0 1
postgres=> SELECT * FROM data1 WHERE c1 = -2 ;
c1  | c2
----+-----
 -2 | add
(1 row)
postgres=> SET constraint_exclusion = on ;
SET
postgres=> SELECT * FROM data1 WHERE c1 = -20 ;
 c1 | c2
----+----
(0 rows)
When you set the parameter constraint_execution to "on", the execution plan will change. Not
published a SQL statement to a remote instance in the case of conditions that do not match CHECK
constraint in the WHERE clause is written.

postgres=> SHOW constraint_exclusion ;
 constraint_exclusion
----------------------
 partition
(1 row)
postgres=> EXPLAIN ANALYZE SELECT COUNT(*) FROM data1 WHERE c1 < 0 ;
                           QUERY PLAN
------------------------------------------------------------------------------------------
 Aggregate  (cost=178.27..178.28 rows=1 width=0) (actual time=1.411..1.411 rows=1 loops=1)
   ->  Foreign Scan on data1  (cost=100.00..175.42 rows=1138 width=0) (actual
time=1.400..1.401 rows=6 loops=1)
 Planning time: 0.267 ms
 Execution time: 2.585 ms
(4 rows)
postgres=> SET constraint_exclusion = on ;
SET
postgres=> EXPLAIN ANALYZE SELECT COUNT(*) FROM data1 WHERE c1 < 0 ;
                            QUERY PLAN
------------------------------------------------------------------------------------------
 Aggregate  (cost=0.01..0.02 rows=1 width=0) (actual time=0.005..0.006 rows=1 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
         One-Time Filter: false
 Planning time: 0.351 ms
 Execution time: 0.038 ms
(5 rows)

postgres=# CREATE FUNCTION rewrite1() RETURNS event_trigger AS $$
BEGIN
  RAISE NOTICE 'Rewriting Table % for reason %',
                pg_event_trigger_table_rewrite_oid()::regclass,
                pg_event_trigger_table_rewrite_reason() ;
END;
$$ LANGUAGE plpgsql ;
CREATE FUNCTION
postgres=# CREATE EVENT TRIGGER rewrite_trg1 ON table_rewrite
EXECUTE PROCEDURE rewrite1() ;
CREATE EVENT TRIGGER
postgres=> ALTER TABLE data1 ALTER COLUMN c1 TYPE VARCHAR(10) ;
NOTICE:  Rewriting Table data1 for reason 4
ALTER TABLE
In the trigger function, it is possible to use the following functions, which get the information of the
trigger’s associated table.

postgres=> CREATE OR REPLACE FUNCTION assert1(NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    ASSERT $1 < 20 ;
    ASSERT $1 > 10, 'Assert Message' ;
    RETURN $1 * 2 ;
END;
$$ LANGUAGE plpgsql ;
CREATE FUNCTION
postgres=> SELECT assert1(30) ;
ERROR:  assertion failed
CONTEXT:  PL/pgSQL function assert2(numeric) line 3 at ASSERT
postgres=> SELECT assert1(5) ;
ERROR:  Assert Message
CONTEXT:  PL/pgSQL function assert2(numeric) line 4 at ASSERT

postgres=> SELECT assert1(5) ;
ERROR:  Assert Message
CONTEXT:  PL/pgSQL function assert1(numeric) line 3 at ASSERT
postgres=> SET plpgsql.check_asserts = false ;
SET
postgres=> SELECT assert1(5) ;
 assert1
---------
      10
(1 row)

postgres=> SELECT '{"key":"key1", "val1":"1000"}'::jsonb || '{"val2":"2000"}' ;
           ?column?
-------------------------------------------------
 {"key": "key1", "val1": "1000", "val2": "2000"}
(1 row)
postgres=> SELECT '{"key":"key1", "val1":"1000"}'::jsonb || '{"val1":"3000"}' ;
       ?column?
---------------------------------
 {"key": "key1", "val1": "3000"}
(1 row)

postgres=> SELECT '{"key":"key1", "val1":"1000"}'::jsonb - 'key' ;
     ?column?
------------------
 {"val1": "1000"}
(1 row)
postgres=> SELECT '["red", "green", "blue"]'::jsonb - 1 ;
    ?column?
-----------------
 ["red", "blue"]
(1 row)
postgres=> SELECT '{"key":"key1", "val1":"1000"}'::jsonb - 'test' ;
            ?column?
---------------------------------
 {"key": "key1", "val1": "1000"}
(1 row)

postgres=> SELECT
'{"ename":{"first":"Mike", "last":"Stern"}, "gender":"M"}'::jsonb
#-
'{"ename","last"}'::text[] ;
                  ?column?
---------------------------------------------
 {"ename": {"first": "Mike"}, "gender": "M"}
(1 row)

postgres=> SELECT
 jsonb_set('{"key":"key1", "val1":"1000"}'::jsonb,'{"val1"}','2000') ;
         jsonb_set
-------------------------------
 {"key": "key1", "val1": 2000}
(1 row)

postgres=> SELECT
  jsonb_set('{"key":"key1", "val1":"1000"}'::jsonb,'{"val2"}','2000') ;
                   jsonb_set
-----------------------------------------------
 {"key": "key1", "val1": "1000", "val2": 2000}
(1 row)

postgres=> SELECT
  jsonb_set('{"key":"key1", "val1":"1000"}'::jsonb,'{"val2"}','2000', false) ;
                   jsonb_set
-----------------------------------------------
 {"key": "key1", "val1": "1000", "val1": 2000}
(1 row)

postgres=> SELECT jsonb_pretty('{"key":"key1", "val1":"1000",
"arr1":["itm1","itm2"]}'::jsonb) ;
    jsonb_pretty
--------------------
 {                 +
     "key": "key1",+
     "arr1": [     +
         "itm1",   +
         "itm2"    +
     ],            +
     "val1": "1000"+
 }
(1 row)

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

postgres=> SELECT generate_series (1.2, 2.1, 0.3) ;
 generate_series
-----------------
             1.2
             1.5
             1.8
             2.1
(4 rows)

postgres=> ALTER TABLE poltbl1 ENABLE ROW LEVEL SECURITY ;
ALTER TABLE
postgres=> \d+ poltbl1
                               Table "public.poltbl1"
 Column |         Type      | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+------------
 c1     | numeric            |        | main     |          |
 c2     | character varying(10) |         | extended |          |
 uname  | character varying(10) |         | extended |          |
Policies (Row Security Enabled): (None)

postgres=> CREATE POLICY pol1 ON poltbl1 FOR ALL USING (uname = current_user) ;
CREATE POLICY
postgres=> \d+ poltbl1
                               Table "public.poltbl1"
Column |         Type       | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+------------
 c1     | numeric            |        | main     |            |
 c2     | character varying(10) |         | extended |            |
 uname  | character varying(10) |         | extended |            |
Policies:
    POLICY "pol1" FOR ALL
      USING (((uname)::name = "current_user"()))
Information of the created policy can be checked from pg_policy catalog. The information of the
table that has been set a policy can be checked from pg_policies catalog.

1
$ psql -U user1
2
postgres=> INSERT INTO poltbl1 VALUES (100, 'Val100', 'user1') ;
3
INSERT 0 1
4
postgres=> INSERT INTO poltbl1 VALUES (200, 'Val200', 'user2') ;
5
INSERT 0 1
6
postgres=> INSERT INTO poltbl1 VALUES (300, 'Val300', 'user3') ;
7
INSERT 0 1
8
postgres=> SELECT COUNT(*) FROM poltbl1 ;
9
 count
10
-------
11
     3
12
(1 row)
13
14
$ psql -U user2
15
postgres=> SELECT * FROM poltbl1 ;
16
c1  |   c2   | uname
17
-----+--------+-------
18
 200 | val200 | user2
19
(1 row)
20
postgres=> UPDATE poltbl1 SET uname='user3' ;
21
ERROR:  new row violates row level security policy for "poltbl1"
CREATE POLICY statement, which create a policy, does not result in an error when you run it to
the table which is not specified ENABLE ROW LEVEL SECURITY clause. In this case, ROW
LEVEL SECURITY feature is not enabled for the corresponding table; only the authorization by
the GRANT statement is enabled.

postgres=> CREATE TABLE brin1 (c1 NUMERIC, c2 NUMERIC, c3 NUMERIC, c4 NUMERIC) ;
CREATE TABLE
postgres=> INSERT INTO brin1 VALUES (
postgres(>   generate_series(1, 10000000),
postgres(>   generate_series(1, 10000000),
postgres(>   generate_series(1, 10000000),
postgres(>   generate_series(1, 10000000)
postgres(>  ) ;
INSERT 0 10000000
Next, create an index for each column. BTREE index is created for the c1 column as a comparison.
From c2 column to c4 column, three BRIN index with different parameter pages_per_range value are
created.

postgres=> CREATE INDEX btree1 ON brin1 (c1) ;
CREATE INDEX
postgres=> CREATE INDEX brin1_def ON brin1 USING BRIN (c2) ;
CREATE INDEX
postgres=> CREATE INDEX brin1_64 ON brin1 USING BRIN (c3)
WITH (pages_per_range = 64) ;
CREATE INDEX
postgres=> CREATE INDEX brin1_512 ON brin1 USING BRIN (c4)
WITH (pages_per_range = 512) ;
CREATE INDEX
postgres=> ANALYZE VERBOSE brin1 ;
INFO:  analyzing "public.brin1"
INFO:  "brin1": scanned 30000 of 73520 pages, containing 4080483 live rows and 0 dead rows;
30000 rows in sample, 9999961 estimated total rows
ANALYZE
postgres=> \d+ brin1
                        Table "public.brin1"
 Column |  Type   | Modifiers | Storage   | Stats target    | Description
--------+---------+-----------+---------+--------------+-------------
 c1     | numeric |           | main    |              |
 c2     | numeric |           | main    |              |
 c3     | numeric |           | main    |              |
 c4     | numeric |           | main    |              |
Indexes:
    "brin1_512" brin (c4) WITH (pages_per_range=512)
    "brin1_64" brin (c3) WITH (pages_per_range=64)
    "brin1_def" brin (c2)
    "btree1" btree (c1)
Finally, checking the usage of storage area. In the case of the example below, BRIN index is found
to be constructed with a very small area.

postgres=> SELECT relname, pg_size_pretty(pg_relation_size(oid)) FROM
pg_class WHERE relname LIKE 'brin1%' OR relname = 'btree1' ;
  relname  | pg_size_pretty
-----------+----------------
 brin1     | 574 MB
 btree1    | 214 MB
 brin1_def  | 32 kB
 brin1_64  | 48 kB
 brin1_512 | 24 kB
(5 rows)
Now I validate the performance. Run the same SQL statements, which have the difference in their
column name, multiple times to verify the results of using the index.

postgres=> EXPLAIN ANALYZE SELECT * FROM brin1 WHERE c1 = 5000000 ;
                       QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using btree1 on brin1  (cost=0.43..8.45 rows=1 width=24)
(actual time=0.085..0.086 rows=1 loops=1)
   Index Cond: (c1 = '5000000'::numeric)
 Planning time: 0.389 ms
 Execution time: 0.240 ms
(4 rows)
The following are examples of using the BRIN index.

postgres=> EXPLAIN ANALYZE SELECT * FROM brin1 WHERE c2 = 5000000 ;
                         QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on brin1  (cost=16.01..20.02 rows=1 width=24)
(actual time=1.955..9.026 rows=1 loops=1)
   Recheck Cond: (c2 = '5000000'::numeric)
   Rows Removed by Index Recheck: 17407
   Heap Blocks: lossy=128
   ->  Bitmap Index Scan on brin1_def  (cost=0.00..16.01 rows=1 width=0)
(actual time=0.747..0.747 rows=1280 loops=1)
         Index Cond: (c2 = '5000000'::numeric)
 Planning time: 0.144 ms
 Execution time: 9.222 ms
(8 rows)

postgres=> EXPLAIN ANALYZE SELECT * FROM brin1 WHERE c3 = 5000000 ;
                       QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on brin1  (cost=24.01..28.02 rows=1 width=24)
(actual time=2.542..5.310 rows=1 loops=1)
   Recheck Cond: (c3 = '5000000'::numeric)
   Rows Removed by Index Recheck: 8703
   Heap Blocks: lossy=64
   ->  Bitmap Index Scan on brin1_64  (cost=0.00..24.01 rows=1 width=0)
(actual time=1.420..1.420 rows=640 loops=1)
         Index Cond: (c3 = '5000000'::numeric)
 Planning time: 0.140 ms
 Execution time: 5.360 ms
(8 rows)

postgres=> EXPLAIN ANALYZE SELECT * FROM brin1 WHERE c4 = 5000000 ;
                        QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on brin1  (cost=12.01..16.02 rows=1 width=24)
(actual time=29.661..36.501 rows=1 loops=1)
   Recheck Cond: (c4 = '5000000'::numeric)
   Rows Removed by Index Recheck: 69631
   Heap Blocks: lossy=512
   ->  Bitmap Index Scan on brin1_512  (cost=0.00..12.01 rows=1 width=0)
(actual time=0.258..0.258 rows=5120 loops=1)
         Index Cond: (c4 = '5000000'::numeric)
 Planning time: 0.146 ms
 Execution time: 36.561 ms
(8 rows)

postgres=# CREATE EXTENSION pageinspect ;
CREATE EXTENSION
postgres=# SELECT itemoffset, blknum, value
   FROM brin_page_items(get_raw_page('brin1_def', 2), 'brin1_def') LIMIT 10 ;
 itemoffset  | blknum  |       value
------------+--------+--------------------
          1 |      0 | {1 .. 18745}
          2 |    128 | {18746 .. 36153}
          3 |    256 | {36154 .. 53561}
          4 |    384 | {53562 .. 70969}
          5 |    512 | {70970 .. 88377}
          6 |    640 | {88378 .. 105785}
          7 |    768 | {105786 .. 123193}
          8 |    896 | {123194 .. 140601}
          9 |   1024 | {140602 .. 158009}
         10 |   1152 | {158010 .. 175417}
(10 rows)
Option information in the BRIN index can also be found from reloptions column of pg_class catalog.

postgres=> SELECT relname,reloptions FROM pg_class where relname like 'brin1%' ;
  relname  |      reloptions
-----------+-----------------------
 brin1     |
 brin1_512 | {pages_per_range=512}
 brin1_64  | {pages_per_range=64}
 brin1_def  |
(4 rows)

$ ps -ef | grep postgres
postgres 12364     1  0 06:14 pts/0 00:00:00 /usr/local/pgsql/bin/postgres -D data
postgres 12365 12364  0 06:14 ?    00:00:00 postgres: cluster1: logger process
postgres 12367 12364  0 06:14 ?    00:00:00 postgres: cluster1: checkpointer process
postgres 12368 12364  0 06:14 ?    00:00:00 postgres: cluster1: writer process
postgres 12369 12364  0 06:14 ?    00:00:00 postgres: cluster1: wal writer process
postgres 12370 12364  0 06:14 ?    00:00:00 postgres: cluster1: autovacuum launcher process
postgres 12371 12364  0 06:14 ?    00:00:00 postgres: cluster1: stats collector process
It does not mean that cluster_name parameter is checked between database clusters. Even if multiple
instance, specified the same value to the cluster_name parameter, are executed in one host, it does not
result in an error.

postgres=> EXPLAIN VERBOSE SELECT * FROM sort1
ORDER BY c1 DESC, c2 COLLATE "C" ;
                      QUERY PLAN
-----------------------------------------------------------------------
 Sort  (cost=65.83..68.33 rows=1000 width=12)
   Output: c1, c2, ((c2)::character varying(10))
   Sort Key: sort1.c1 DESC, sort1.c2 COLLATE "C"
   ->  Seq Scan on public.sort1  (cost=0.00..16.00 rows=1000 width=12)
         Output: c1, c2, c2

LOG:  received replication command: IDENTIFY_SYSTEM
LOG:  received replication command: START_REPLICATION SLOT "slot_1" 0/7000000 TIMELINE 1
Log is not output at the time of the slave instance stop. Since pg_basebackup command also uses the
replication feature, the following log is output.

LOG:  received replication command: IDENTIFY_SYSTEM
LOG:  received replication command: BASE_BACKUP LABEL 'pg_basebackup base backup'  WAL
NOWAIT

postgres=> SELECT relnamespace::regnamespace, relname, relowner::regrole
FROM pg_class WHERE relnamespace = 'public'::regnamespace ;
relnamespace | relname | relowner
--------------+---------+----------
public       | data1   | user1
 public       | data2   | user1
(2 rows)