Postgres - logical replication

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

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

IIS disable greedy cache on symlinks

a shortcut to: https://stackoverflow.com/questions/6698481/caching-problem-with-symlinks-on-iis-7-5

same behavior with IIS 8 on win 10. problems manifest when sharing some maphive js libs between projects via symlinks (mklink j/d/)

basically disabling cache on the site or IIS lvl does not seem to help with symlinks.

adding an entry to registry does the trick though:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\InetInfo\Parameters]
"DisableMemoryCache"=dword:1

psql client encoding

psql ERROR: character with byte sequence in encoding "WIN1252" has no equivalent in encoding "UTF8"


SET CLIENT_ENCODING TO 'utf8';

missing netstandard.dll

<<Reference Include="netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51">
    <HintPath>C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\MSBuild\Microsoft\Microsoft.NET.Build.Extensions\net461\lib\netstandard.dll</HintPath>
    <Private>True</Private>
  </Reference>

System.IO.FileLoadException: 'Could not load file or assembly "System.ValueTuple, Version=0.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51" or one of its dependencies. The definition of the assembly manifest found does not match the reference to the assembly.

a 'rewrite' from: https://stackoverflow.com/questions/44844798/could-not-load-file-or-assembly-system-valuetuple-version-0-0-0-0-or-one-of-i

System.IO.FileLoadException: 'Could not load file or assembly "System.ValueTuple, Version=0.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51" or one of its dependencies. The definition of the assembly manifest found does not match the reference to the assembly.

Edit .csproj:

<propertygroup>
  <autogeneratebindingredirects>true</autogeneratebindingredirects>
  <generatebindingredirectsoutputtype>true</generatebindingredirectsoutputtype>
</propertygroup>

this should result in automated rewrite in ap.config / web.config that is an equivalent to:

<assemblybinding xmlns="urn:schemas-microsoft-com:asm.v1">
  <dependentassembly>
    <assemblyidentity name="System.ValueTuple" publickeytoken="cc7b13ffcd2ddd51" culture="neutral"></assemblyidentity>
    <bindingredirect oldversion="0.0.0.0-4.0.2.0" newversion="4.0.2.0"></bindingredirect>
  </dependentassembly>
</assemblybinding>