Configuration missing on pg_hba.conf ?

[orondon]

ornellas-mbp:postgresbdr orondon$ docker version
Client:
Version: 17.09.0-ce
API version: 1.32
Go version: go1.8.3
Git commit: afdb6d4
Built: Tue Sep 26 22:40:09 2017
OS/Arch: darwin/amd64

Server:
Version: 17.09.0-ce
API version: 1.32 (minimum version 1.12)
Go version: go1.8.3
Git commit: afdb6d4
Built: Tue Sep 26 22:45:38 2017
OS/Arch: linux/amd64
Experimental: true
ornellas-mbp:postgresbdr orondon$ docker info
Containers: 0
Running: 0
Paused: 0
Stopped: 0
Images: 27
Server Version: 17.09.0-ce
Storage Driver: overlay2
Backing Filesystem: extfs
Supports d_type: true
Native Overlay Diff: true
Logging Driver: json-file
Cgroup Driver: cgroupfs
Plugins:
Volume: local
Network: bridge host ipvlan macvlan null overlay
Log: awslogs fluentd gcplogs gelf journald json-file logentries splunk syslog
Swarm: inactive
Runtimes: runc
Default Runtime: runc
Init Binary: docker-init
containerd version: 06b9cb35161009dcb7123345749fef02f7cea8e0
runc version: 3f2f8b84a77f73d38244dd690525642a72156c64
init version: 949e6fa
Security Options:
seccomp
Profile: default
Kernel Version: 4.9.49-moby
Operating System: Alpine Linux v3.5
OSType: linux
Architecture: x86_64
CPUs: 4
Total Memory: 1.952GiB
Name: moby
ID: U6LD:JS66:5NCB:XI36:KWYD:BEEB:EZRJ:QWIR:D7KG:4FZS:KFHJ:ZVQM
Docker Root Dir: /var/lib/docker
Debug Mode (client): false
Debug Mode (server): true
File Descriptors: 19
Goroutines: 31
System Time: 2017-11-17T20:39:08.286208735Z
EventsListeners: 1
No Proxy: *.local, 169.254/16
Registry: https://index.docker.io/v1/
Experimental: true
Insecure Registries:
127.0.0.0/8
Live Restore Enabled: false

Commands

docker run --name postgres --net=welcome-smsx --volume /Users/orondon/Documents/DockerProjects/postgresbdr/postgresql:/var/lib/postgresql -itd --restart always markrowsoft/postgresql-bdr

docker run --name postgres2 --net=welcome-smsx --volume /Users/orondon/Documents/DockerProjects/postgresbdr/postgresql:/var/lib/postgresql -itd --restart always markrowsoft/postgresql-bdr

On System Version: macOS 10.13.1 (17B48)
Kernel Version: Darwin 17.2.0

This is what I get when setting up BDR

wsmsxdb=# CREATE EXTENSION IF NOT EXISTS btree_gist; CREATE EXTENSION IF NOT EXISTS bdr;
NOTICE: extension "btree_gist" already exists, skipping
CREATE EXTENSION
CREATE EXTENSION
wsmsxdb=# SELECT bdr.bdr_group_create( local_node_name := 'node01', node_external_dsn := 'host=172.18.0.2 port=5432 dbname=wsmsxdb' );
FATAL: could not connect to the server in non-replication mode: fe_sendauth: no password supplied

DETAIL: dsn was: connect_timeout=30 keepalives=1 keepalives_idle=20 keepalives_interval=20 keepalives_count=5 host=172.18.0.2 port=5432 dbname=wsmsxdb fallback_application_name='bdr (6489480670487482405,1,16384,):bdrnodeinfo'
CONTEXT: SQL statement "SELECT * FROM bdr_get_remote_nodeinfo(node_local_dsn)"
PL/pgSQL function internal_begin_join(text,text,text,text) line 42 at SQL statement
SQL statement "SELECT bdr.internal_begin_join(
'bdr_group_join',
local_node_name,
CASE WHEN node_local_dsn IS NULL THEN node_external_dsn ELSE node_local_dsn END,
join_using_dsn)"
PL/pgSQL function bdr_group_join(text,text,text,text,integer,text[]) line 21 at PERFORM
SQL statement "SELECT bdr.bdr_group_join(
local_node_name := local_node_name,
node_external_dsn := node_external_dsn,
join_using_dsn := null,
node_local_dsn := node_local_dsn,
apply_delay := apply_delay,
replication_sets := replication_sets)"
PL/pgSQL function bdr_group_create(text,text,text,integer,text[]) line 84 at PERFORM
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Online forums suggest to alter pg_hba.conf to add
host replication postgres 127.0.0.1/0 trust
If I try that then restart the container, it won’t start up

ornellas-mbp:postgresbdr orondon$ docker logs wsmsx-postgres
Initializing datadir...
Initializing certdir...
Initializing logdir...
Initializing rundir...
Setting resolv.conf ACLs...
pg_ctl: could not start server
Examine the log output.
Initializing datadir...
Initializing certdir...
Initializing logdir...
Initializing rundir...
Setting resolv.conf ACLs...
pg_ctl: could not start server
Examine the log output.
Initializing datadir...

Is there any reason why the original image doesn’t have pg_hba.conf replication configuration?
Could you please help out?

My other issue is that I’m trying to load a sql dump file into the database. Any suggestions as to when to do this, before/after BDR configuration?

Thanks a lot!
Ornella

[markrowsoft]

Currently working so the quick answer is take a look at your pga_hba.conf file... you have to add a line to allow replication and access for master/slave ips. Also just a note if you are using a dump from a regular postgresql db that sequences need to be created differently or it can cause issues later: http://bdr-project.org/docs/stable/global-sequence-usage.html

…

On Fri, Nov 17, 2017 at 3:52 PM, orondon ***@***.***> wrote: ornellas-mbp:postgresbdr orondon$ docker version Client: Version: 17.09.0-ce API version: 1.32 Go version: go1.8.3 Git commit: afdb6d4 Built: Tue Sep 26 22:40:09 2017 OS/Arch: darwin/amd64 Server: Version: 17.09.0-ce API version: 1.32 (minimum version 1.12) Go version: go1.8.3 Git commit: afdb6d4 Built: Tue Sep 26 22:45:38 2017 OS/Arch: linux/amd64 Experimental: true ornellas-mbp:postgresbdr orondon$ docker info Containers: 0 Running: 0 Paused: 0 Stopped: 0 Images: 27 Server Version: 17.09.0-ce Storage Driver: overlay2 Backing Filesystem: extfs Supports d_type: true Native Overlay Diff: true Logging Driver: json-file Cgroup Driver: cgroupfs Plugins: Volume: local Network: bridge host ipvlan macvlan null overlay Log: awslogs fluentd gcplogs gelf journald json-file logentries splunk syslog Swarm: inactive Runtimes: runc Default Runtime: runc Init Binary: docker-init containerd version: 06b9cb35161009dcb7123345749fef02f7cea8e0 runc version: 3f2f8b84a77f73d38244dd690525642a72156c64 init version: 949e6fa Security Options: seccomp Profile: default Kernel Version: 4.9.49-moby Operating System: Alpine Linux v3.5 OSType: linux Architecture: x86_64 CPUs: 4 Total Memory: 1.952GiB Name: moby ID: U6LD:JS66:5NCB:XI36:KWYD:BEEB:EZRJ:QWIR:D7KG:4FZS:KFHJ:ZVQM Docker Root Dir: /var/lib/docker Debug Mode (client): false Debug Mode (server): true File Descriptors: 19 Goroutines: 31 System Time: 2017-11-17T20:39:08.286208735Z EventsListeners: 1 No Proxy: *.local, 169.254/16 Registry: https://index.docker.io/v1/ Experimental: true Insecure Registries: 127.0.0.0/8 Live Restore Enabled: false Commands docker run --name postgres --net=welcome-smsx --volume /Users/orondon/Documents/DockerProjects/postgresbdr/postgresql:/var/lib/postgresql -itd --restart always markrowsoft/postgresql-bdr docker run --name postgres2 --net=welcome-smsx --volume /Users/orondon/Documents/DockerProjects/postgresbdr/postgresql:/var/lib/postgresql -itd --restart always markrowsoft/postgresql-bdr On System Version: macOS 10.13.1 (17B48) Kernel Version: Darwin 17.2.0 This is what I get when setting up BDR wsmsxdb=# CREATE EXTENSION IF NOT EXISTS btree_gist; CREATE EXTENSION IF NOT EXISTS bdr; NOTICE: extension "btree_gist" already exists, skipping CREATE EXTENSION CREATE EXTENSION wsmsxdb=# SELECT bdr.bdr_group_create( local_node_name := 'node01', node_external_dsn := 'host=172.18.0.2 port=5432 dbname=wsmsxdb' ); FATAL: could not connect to the server in non-replication mode: fe_sendauth: no password supplied DETAIL: dsn was: connect_timeout=30 keepalives=1 keepalives_idle=20 keepalives_interval=20 keepalives_count=5 host=172.18.0.2 port=5432 dbname=wsmsxdb fallback_application_name='bdr (6489480670487482405,1,16384,) :bdrnodeinfo' CONTEXT: SQL statement "SELECT * FROM bdr_get_remote_nodeinfo(node_ local_dsn)" PL/pgSQL function internal_begin_join(text,text,text,text) line 42 at SQL statement SQL statement "SELECT bdr.internal_begin_join( 'bdr_group_join', local_node_name, CASE WHEN node_local_dsn IS NULL THEN node_external_dsn ELSE node_local_dsn END, join_using_dsn)" PL/pgSQL function bdr_group_join(text,text,text,text,integer,text[]) line 21 at PERFORM SQL statement "SELECT bdr.bdr_group_join( local_node_name := local_node_name, node_external_dsn := node_external_dsn, join_using_dsn := null, node_local_dsn := node_local_dsn, apply_delay := apply_delay, replication_sets := replication_sets)" PL/pgSQL function bdr_group_create(text,text,text,integer,text[]) line 84 at PERFORM server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. Online forums suggest to alter pg_hba.conf to add host replication postgres 127.0.0.1/0 trust If I try that then restart the container, it won’t start up ornellas-mbp:postgresbdr orondon$ docker logs wsmsx-postgres Initializing datadir... Initializing certdir... Initializing logdir... Initializing rundir... Setting resolv.conf ACLs... pg_ctl: could not start server Examine the log output. Initializing datadir... Initializing certdir... Initializing logdir... Initializing rundir... Setting resolv.conf ACLs... pg_ctl: could not start server Examine the log output. Initializing datadir... Is there any reason why the original image doesn’t have pg_hba.conf replication configuration? Could you please help out? My other issue is that I’m trying to load a sql dump file into the database. Any suggestions as to when to do this, before/after BDR configuration? Thanks a lot! Ornella — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#3>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAwTH266Dadf1cV3w-TcY00zrQzRYUNEks5s3fHygaJpZM4Qim27> .

-- Mark https://www.linkedin.com/in/markengstrom

[orondon]

thanks! managed adding this line to pga_hba.conf
host replication postgres 0.0.0.0/0 trust
and restarting the container.

now I try the next step and getting the error below

SELECT bdr.bdr_group_create( local_node_name := 'node01', node_external_dsn := 'host=172.18.0.2 port=5432 dbname=wsmsxdb' );
FATAL: could not connect to the server in non-replication mode: fe_sendauth: no password supplied

DETAIL: dsn was: connect_timeout=30 keepalives=1 keepalives_idle=20 keepalives_interval=20 keepalives_count=5 host=172.18.0.2 port=5432 dbname=wsmsxdb fallback_application_name='bdr (6490582663624970277,1,16385,):bdrnodeinfo'
CONTEXT: SQL statement "SELECT * FROM bdr_get_remote_nodeinfo(node_local_dsn)"
PL/pgSQL function internal_begin_join(text,text,text,text) line 42 at SQL statement
SQL statement "SELECT bdr.internal_begin_join(
'bdr_group_join',
local_node_name,
CASE WHEN node_local_dsn IS NULL THEN node_external_dsn ELSE node_local_dsn END,
join_using_dsn)"
PL/pgSQL function bdr_group_join(text,text,text,text,integer,text[]) line 21 at PERFORM
SQL statement "SELECT bdr.bdr_group_join(
local_node_name := local_node_name,
node_external_dsn := node_external_dsn,
join_using_dsn := null,
node_local_dsn := node_local_dsn,
apply_delay := apply_delay,
replication_sets := replication_sets)"
PL/pgSQL function bdr_group_create(text,text,text,integer,text[]) line 84 at PERFORM
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

some online forum suggested to create the following in the slave
cat > ~/.pgpass <<EOS
${MASTER_PORT_5432_TCP_ADDR}:5432:replication:${REP_USER}:${REP_PASS}
EOS

Using the docker ip addr, But this approach didn't work.

Also I realized I was using the same local directory for data persistence in both master and slave, now using separate ones, correct?

Do you have any input that can help me out?

Haven't been able to try the sequence yet, but appreciate the advice.

[markrowsoft]

Try something like node_external_dsn := 'host=172.18.0.2 port=5432 dbname=wsmsxdb password=passwdUset’ Would have to look at the docker file to see what I had used. From: orondon <notifications@github.com> Reply-To: markrowsoft/docker-postgres-bdr <reply@reply.github.com> Date: Monday, November 20, 2017 at 3:16 PM To: markrowsoft/docker-postgres-bdr <docker-postgres-bdr@noreply.github.com> Cc: Mark Engstrom <mark@markrowsoft.com>, Comment <comment@noreply.github.com> Subject: Re: [markrowsoft/docker-postgres-bdr] Configuration missing on pg_hba.conf ? (#3) thanks! managed adding this line to pga_hba.conf host replication postgres 0.0.0.0/0 trust and restarting the container. now I try the next step and getting the error below SELECT bdr.bdr_group_create( local_node_name := 'node01', node_external_dsn := 'host=172.18.0.2 port=5432 dbname=wsmsxdb' ); FATAL: could not connect to the server in non-replication mode: fe_sendauth: no password supplied DETAIL: dsn was: connect_timeout=30 keepalives=1 keepalives_idle=20 keepalives_interval=20 keepalives_count=5 host=172.18.0.2 port=5432 dbname=wsmsxdb fallback_application_name='bdr (6490582663624970277,1,16385,):bdrnodeinfo' CONTEXT: SQL statement "SELECT * FROM bdr_get_remote_nodeinfo(node_local_dsn)" PL/pgSQL function internal_begin_join(text,text,text,text) line 42 at SQL statement SQL statement "SELECT bdr.internal_begin_join( 'bdr_group_join', local_node_name, CASE WHEN node_local_dsn IS NULL THEN node_external_dsn ELSE node_local_dsn END, join_using_dsn)" PL/pgSQL function bdr_group_join(text,text,text,text,integer,text[]) line 21 at PERFORM SQL statement "SELECT bdr.bdr_group_join( local_node_name := local_node_name, node_external_dsn := node_external_dsn, join_using_dsn := null, node_local_dsn := node_local_dsn, apply_delay := apply_delay, replication_sets := replication_sets)" PL/pgSQL function bdr_group_create(text,text,text,integer,text[]) line 84 at PERFORM server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. some online forum suggested to create the following in the slave cat > ~/.pgpass <<EOS ${MASTER_PORT_5432_TCP_ADDR}:5432:replication:${REP_USER}:${REP_PASS} EOS Using the docker ip addr, But this approach didn't work. Also I realized I was using the same local directory for data persistence in both master and slave, now using separate ones, correct? Do you have any input that can help me out? Haven't been able to try the sequence yet, but appreciate the advice. — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#3 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AAwTH80wUJUvDKx4Rs3YJOySChzHyvtKks5s4d4fgaJpZM4Qim27>.

[markrowsoft]

Sorry … it’s not set for bdr in docker file … its in this line on the master db…. Just set the password to what you want in that Query: SELECT bdr.bdr_group_create( local_node_name := 'node01', node_external_dsn := 'host=172.18.0.2 port=5432 dbname=wsmsxdb password=pass123' ); Then make sure it matches when you setup the slave. From: orondon <notifications@github.com> Reply-To: markrowsoft/docker-postgres-bdr <reply@reply.github.com> Date: Monday, November 20, 2017 at 3:16 PM To: markrowsoft/docker-postgres-bdr <docker-postgres-bdr@noreply.github.com> Cc: Mark Engstrom <mark@markrowsoft.com>, Comment <comment@noreply.github.com> Subject: Re: [markrowsoft/docker-postgres-bdr] Configuration missing on pg_hba.conf ? (#3) thanks! managed adding this line to pga_hba.conf host replication postgres 0.0.0.0/0 trust and restarting the container. now I try the next step and getting the error below SELECT bdr.bdr_group_create( local_node_name := 'node01', node_external_dsn := 'host=172.18.0.2 port=5432 dbname=wsmsxdb' ); FATAL: could not connect to the server in non-replication mode: fe_sendauth: no password supplied DETAIL: dsn was: connect_timeout=30 keepalives=1 keepalives_idle=20 keepalives_interval=20 keepalives_count=5 host=172.18.0.2 port=5432 dbname=wsmsxdb fallback_application_name='bdr (6490582663624970277,1,16385,):bdrnodeinfo' CONTEXT: SQL statement "SELECT * FROM bdr_get_remote_nodeinfo(node_local_dsn)" PL/pgSQL function internal_begin_join(text,text,text,text) line 42 at SQL statement SQL statement "SELECT bdr.internal_begin_join( 'bdr_group_join', local_node_name, CASE WHEN node_local_dsn IS NULL THEN node_external_dsn ELSE node_local_dsn END, join_using_dsn)" PL/pgSQL function bdr_group_join(text,text,text,text,integer,text[]) line 21 at PERFORM SQL statement "SELECT bdr.bdr_group_join( local_node_name := local_node_name, node_external_dsn := node_external_dsn, join_using_dsn := null, node_local_dsn := node_local_dsn, apply_delay := apply_delay, replication_sets := replication_sets)" PL/pgSQL function bdr_group_create(text,text,text,integer,text[]) line 84 at PERFORM server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. some online forum suggested to create the following in the slave cat > ~/.pgpass <<EOS ${MASTER_PORT_5432_TCP_ADDR}:5432:replication:${REP_USER}:${REP_PASS} EOS Using the docker ip addr, But this approach didn't work. Also I realized I was using the same local directory for data persistence in both master and slave, now using separate ones, correct? Do you have any input that can help me out? Haven't been able to try the sequence yet, but appreciate the advice. — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#3 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AAwTH80wUJUvDKx4Rs3YJOySChzHyvtKks5s4d4fgaJpZM4Qim27>.