search

markrowsoft / docker-postgres-bdr

PostgreSQL docker image with bdr support
MIT License
2 stars 1 forks source link

Configuration missing on pg_hba.conf ? #3

Open orondon opened 6 years ago

orondon commented 6 years ago

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 commented 6 years ago

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 notifications@github.com 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_remotenodeinfo(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 https://github.com/markrowsoft/docker-postgres-bdr/issues/3, or mute the thread https://github.com/notifications/unsubscribe-auth/AAwTH266Dadf1cV3w-TcY00zrQzRYUNEks5s3fHygaJpZM4Qim27 .

--

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

orondon commented 6 years ago

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 commented 6 years ago

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 GitHubhttps://github.com/markrowsoft/docker-postgres-bdr/issues/3#issuecomment-345818145, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAwTH80wUJUvDKx4Rs3YJOySChzHyvtKks5s4d4fgaJpZM4Qim27.

markrowsoft commented 6 years ago

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 GitHubhttps://github.com/markrowsoft/docker-postgres-bdr/issues/3#issuecomment-345818145, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAwTH80wUJUvDKx4Rs3YJOySChzHyvtKks5s4d4fgaJpZM4Qim27.