cartoninjas.net

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'

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'

Master DB:

1. Create a publication

CREATE PUBLICATION alltables FOR ALL TABLES;

2. Create a replication user

create role replication_user with login password 'some-pass' replication; 
--GRANT whatever permissions are required
grant usage on schema some_schema to replication_user;
grant select on all tables in schema some_schema to replication_user;

Note: with logical replication, user does not seem to have to have the replication rights; this is required with std streaming replication

3. Modify the postgresql.conf to allow connections to the machine via its ip

listen_addresses = 'localhost,server_ip'

and set up a proper WAL lvl

wal_level = logical			# minimal, replica, or logical

4. Modify pg_hba.conf to allow connections from specified slaves

host    all     replication_user         slave_ip/0                 md5

5. Dump db structure

pg_dump.exe --host localhost --port 5432 --username "some-user-name" --no-password  --format plain --schema-only --verbose --file "C:\temp\db_schema.sql" --schema "schema-name" "db-name"

6. Review publication and replication slots (when a subscription is created)

select * from pg_publication;
select * from pg_replication_slots; --when subscription is created

--when need to drop replication slot
select pg_drop_replication_slot('slot_name')

7. Check replication status

select * from pg_publication;
select * from pg_stat_replication;

Replica DB:

1. recreate db structure - this is required in order to make the subscription work

createdb --host localhost --port 5432 --username user-name  db-name
psql --host localhost --port 5432 --username user-name -w --dbname db-name --command "create extension if not exists postgis"
psql --host localhost --port 5434 --username postgres -w --dbname db-name --file "c:\temp\db_schema.sql"

Note: installing postgis is not required of course

2. Create a subscription

create subscription my_sub 
connection 'host=master-ip dbname=db-to-replicate user=replication_user password=some-password port=5432' 
publication alltables;

3. Review subscription

select * from pg_subscription;

4. Check replication status

select * from pg_stat_subscription;

More info here: https://www.postgresql.org/docs/10/logical-replication.html

SELECT
    row_to_json(feature_collection)
FROM (
    SELECT
        'FeatureCollection'::text as type,
        case
            when
                array_agg(feature_collection_data) is null
            then
                array_to_json(ARRAY[]::varchar[]) 
            else
                array_to_json(array_agg(feature_collection_data))
        end as features
    FROM (
        SELECT
            'Feature'::text as type,
            ST_AsGeoJSON({geomCol})::json as geometry,
            row_to_json((SELECT row FROM (SELECT {properties}) as row)) as properties
        FROM
            {table} as row_data
        WHERE
            {where}
    )  as feature_collection_data
)  as feature_collection;";

Notes:

{geomCol} - geom column to select from or geometry returning call such as ST_GeomFromText('POINT(' || lon || ' ' || lat || ')' ,4326)

{properties} - a comma separated list of columns to select from; can be aliased

{table} - a table to select from

{where} - a where clause if needed

fire up psql with proper credentials and execute

 \i [path-to-the-db-dump.sql]