Re: Accessing Postgres Server and database from other Machine

Hi Adrian,

> On 04. Dec, 2020, at 16:13, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> That is the wrong file, the *.sample is the giveaway.

hmmm, I'd rather call it essential reference documentation or template for automation. It's perfectly well suited to automatically strip all comments and then diff the result to ones real world postgresql.conf or some other version postgresql.conf file to find parameters that have been removed or changed with a new PostgreSQL version. This is highly useful for planning migrations and have a quick reference what to check for before actually migrating. So for me this is much more than just a giveaway.

A simple, but effective (Linux, bash) example:

#!/bin/bash
oldHome=/data/postgres/12.5
newHome=/data/postgres/13.1
# confOld=${oldHome}/share/postgresql.conf.sample
confOld=/data/pg01/cdb01a/db/postgresql.base.conf # postgresql."base".conf because of Patroni
confNew=${newHome}/share/postgresql.conf.sample
sed -e "s/^#//; s/[[:space:]]*#.*$//; /^--*/d; /^ /d; /^$/d" ${confOld} | sort >/tmp/f1
sed -e "s/^#//; s/[[:space:]]*#.*$//; /^--*/d; /^ /d; /^$/d" ${confNew} | sort >/tmp/f2
diff -y /tmp/f1 /tmp/f2
rm /tmp/f1 /tmp/f2

Cheers,
Paul

Here's some sample output of my (still) 12.5 Patroni/etcd test-and-play-around-cluster run against the new PostgreSQL 13.1 sample file:

archive_command = 'cp %p /data/arch/cdb01a/%f' | archive_cleanup_command = ''
archive_mode = on | archive_command = ''
archive_timeout = 1800 | archive_mode = off
> archive_timeout = 0
> array_nulls = on
> authentication_timeout = 1min
autovacuum_analyze_scale_factor = 0.1 autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50 autovacuum_analyze_threshold = 50
autovacuum_freeze_max_age = 200000000 autovacuum_freeze_max_age = 200000000
autovacuum_max_workers = 10 | autovacuum_max_workers = 3
autovacuum_multixact_freeze_max_age = 400000000 autovacuum_multixact_freeze_max_age = 400000000
autovacuum_naptime = 60s | autovacuum_naptime = 1min
autovacuum = on autovacuum = on
> autovacuum_vacuum_cost_delay = 2ms
> autovacuum_vacuum_cost_limit = -1
> autovacuum_vacuum_insert_scale_factor = 0.2
> autovacuum_vacuum_insert_threshold = 1000
autovacuum_vacuum_scale_factor = 0.2 autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 50 autovacuum_vacuum_threshold = 50
checkpoint_timeout = 30s | autovacuum_work_mem = -1
...
and so on