pgsql - list tables with column names

Recently I had to automate postgis export via ogr2ogr. In order to list all the sptatial table a following query came in handy: 

select
	t.table_name,
	c.column_name
from
	information_schema.tables t
	left outer join information_schema.columns c on t.table_name = c.table_name
where
	t.table_schema = 'public' and
	t.table_name like 'vw_%' and
	c.column_name = 'wkb_geometry'

and the actual command generation

select
	'ogr2ogr -f "ESRI Shapefile" d:\temp\' || t.table_name || '.shp PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" "' || t.table_name || '"'
from
	information_schema.tables t
	left outer join information_schema.columns c on t.table_name = c.table_name
where
	t.table_schema = 'public' and
	t.table_name like 'vw_%' and
	c.column_name = 'wkb_geometry'

ERROR: could not stat file “XX.csv”: Unknown error

a shortcut to https://stackoverflow.com/questions/53523051/error-could-not-stat-file-xx-csv-unknown-error/53533574

COPY XXX FROM 'D:/XXX.csv'  WITH (FORMAT CSV, HEADER TRUE, NULL 'NULL')
[Code: 0, SQL State: XX000]  ERROR: could not stat file "'D:/XXX.csv'  Unknown error
copy t(c,d) from program 'cmd /c "type x:\path\to\file.txt"' with (format text);

postgres streaming replication

master db

1. setup pg_hba.conf so remote connection is allowed (requires db restart)

add:

host    replication     replication_user         S.OM.E.IP/0                 md5
host    replication     replication_user         S.OM.E.IP/0                 md5

notice the name of the db - replication:

The value replication specifies that the record matches if a physical replication connection is requested (note that replication connections do not specify any particular database). More in postgres docs.


2. configure firewall to allow incoming traffic from the replica server for each port dbs are listening on; add the replica ip to the white list


3. adjust postgresql.conf (requires db restart)

wal_level = replica
max_wal_senders = 10
wal_keep_segments = 10
hot_standby = on
primary_conninfo = 'host=S.OM.E.IP port=some_port user=replication_user password=some_pass'
primary_slot_name = 'physical_slot_1'


4. Create replication role

create role replication_user with login password 'some_pass' replication;


5. Create replication slot

SELECT * FROM pg_create_physical_replication_slot('physical_slot_1');


replica db

1. backup and restore dbs on replica server (requires the db data folders to be empty, as all the stuff should be brought over)

pg_basebackup --host=S.OM.E.IP --port=some_port --username=replication_user --wal-method=fetch --pgdata=destination_dir

create standby.signal file in the data dir of each database


2. start replica; logs should show the replication is working:

2020-04-17 20:16:18.224 CEST [9276] LOG:  entering standby mode
2020-04-17 20:16:18.290 CEST [9276] LOG:  redo starts at 0/2000028
2020-04-17 20:16:18.323 CEST [9276] LOG:  consistent recovery state reached at 0/2000138
2020-04-17 20:16:18.326 CEST [19868] LOG:  database system is ready to accept read only connections
2020-04-17 20:16:18.416 CEST [15924] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1

IIS ARR Rewrite 502.3 Bad Gateway

If you happen to hit the err below, when rewriting to local server with ssl,

HTTP Error 502.3 - Bad Gateway

A security error occurred

Most likely causes:

  • The CGI application did not return a valid set of HTTP errors.
  • A server acting as a proxy or gateway was unable to process the request due to an error in a parent gateway.

Things you can try:

  • Use DebugDiag to troubleshoot the CGI application.
  • Determine if a proxy or gateway is responsible for this error.

try disabling some of the ssl security as specified here: https://docs.microsoft.com/en-us/iis/extensions/configuring-application-request-routing-arr/arr-support-added-for-winhttpoptionsecurityflags


reg.exe add "HKLM\SOFTWARE\Microsoft\IIS Extensions\Application Request Routing\Parameters" /v SecureConnectionIgnoreFlags /t REG_DWORD /d 0

and then set the value to 0x00003300

expose postgres to outer world

fire wall

Whatever you need for firewall...

pg_hba.conf

host    all             all             XXX.XXX.XXX.XXX/0           md5

postgresql.conf

listen_addresses = 'localhost,XXX.XXX.XXX.XXX'