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

Installing mongodb as a service on windows

  1. Get the latest version of mongodb from here.
  2. Unpack the content of the archive wherever you need.
    1. [edit] looked at v3 at some point and could not find the binary - only msi available. in order to unpack msi use 7zip or type the following in the cmd (you may need admin rights); instaling msi will also extract it ;)
    2. msiexec /a filepath to MSI file /qb TARGETDIR=filepath to target folder
      
  3. Create a directory for the db data
  4. Prepare a mongodb config, for example:
  5. systemLog:
        destination: file
        path: "h:\\ere\\goes\\path\\to\\the\\logfile.log"
        quiet: true
        logAppend: true
    storage:
        journal:
            enabled: true
    net:
        port: 10005
        bindIp: 1270.0.0.1
    storage:
        dbPath: h:\\ere\\goes\\db\\path\\
    • starting with 2.6 cfg is in yaml, the old syntax is compatible though
    • spaces are used for indentation, tabs will not do (see)
    • paths need to be escaped
  6. Install mongodb as a service:
  7. mongod.exe --config h:\ere\goes\the\cfg\path --install
    
    • By default --install command will create a service named MongoDB; in order to adjust it have a look at the --serviceName, --serviceDisplayName and --serviceDescription params
    • By default the service will be configured to be runned under the ".\LocalSystem" not the "NT AUTHORITY\NetworkService" account as one coulde expect; If you want to specify a service user / pass use the --serviceUser and --servicePassword args

  8. And start it either through the services tab of the task manager or from the command line:
  9. net start serviceName


The service can be stopped through the services tab or from the command line:

net stop serviceName


To uninstall the service run:

mongod.exe --remove --serviceName sName

IIS - multiple host aliases for the loopback adapter 127.0.0.1

Scenario: ARR listening on port 80 set to load balance multiple sites listening on different ports on local host

Problem: Server Farm will refuse to add multiple localhost addresses - it does not use port number to differentiate between host aliases

Solution: locate the Windows\System32\drivers\etc\hosts file and add for example the following:

127.0.0.1	localhost1
127.0.0.1	localhost2
127.0.0.1	localhost3
127.0.0.1	localhost4

This will make the above aliases point to localhost

Installing redis as a service on windows

A quick reminder (mainly for myself) on how to setup redis on windows

  1. Get the latest release of redis from https://github.com/MSOpenTech/redis (downloading from github may be tricky, so in order to download https://github.com/MSOpenTech/redis/tree/2.8/bin/release/redis-2.8.12.zip just go to https://github.com/MSOpenTech/redis/raw/2.8/bin/release/redis-2.8.12.zip; 2.8.12 was the current at the time of writing so the links will die at some point); archive contains a step by step guide on how to set up the redis, so you can actually skip reading already ;)
  2. Extract the executables to some location of choice, for example c:\redis\version
  3. Create a folder for the db data (by default redis will store the files where the executables reside)
  4. Copy the redis.windows.conf and rename it as needed - this is a redis instance cfg; some settings worth considering:
    • dir - specifies the db files folder (by default redis will store the files where the executables reside)
    • port - the default one is 6379
    • maxmemory / maxheap - from the cfg itself: the maxheap flag controls the maximum size of this memory mapped file, as well as the total usable space for the Redis heap. Running Redis without either maxheap or maxmemory will result in a memory mapped file being created that is equal to the size of physical memory; The Redis heap must be larger than the value specified by the maxmemory
  5. Create a service using the redis-server.exe (the service will be configured as autostart and will be launched as "NT AUTHORITY\NetworkService"):
  6. redis-server --service-install c:\redis\data\mydb\redis.conf --service-name testRedisService

This is pretty much it. The service should be properly configured and just needs to be started!