Skip to content

Instantly share code, notes, and snippets.

@volkanunsal
Last active September 28, 2017 07:23
Show Gist options
  • Save volkanunsal/ad2173e2649393fcd3b6 to your computer and use it in GitHub Desktop.
Save volkanunsal/ad2173e2649393fcd3b6 to your computer and use it in GitHub Desktop.
docker-postgis setup with streaming replication
master:
image: mdillon/postgis:9.3
ports:
- '5432:5432'
volumes:
- /import_data
- ./postgis/init-master.sh:/docker-entrypoint-initdb.d/init-master.sh
- ./postgis:/tmp/postgresql
restart: always
environment:
POSTGRES_PASSWORD: password
DB_NAME: gis
DB_USER: someuser
DB_PASSWORD: password
MASTER_PORT_5432_TCP_ADDR: <master IP address>
REP_USER: replication
REP_PASS: password
SLAVE_PORT_5432_TCP_ADDR: <slave IP address>
PGARCHIVE: "/var/lib/postgresql/archive"
slave:
image: mdillon/postgis:9.3
ports:
- '5432:5432'
volumes:
- /import_data
- ./postgis/init-slave.sh:/docker-entrypoint-initdb.d/init-slave.sh
- ./postgis:/tmp/postgresql
restart: always
environment:
POSTGRES_PASSWORD: password
DB_NAME: gis
DB_USER: someuser
DB_PASSWORD: password
MASTER_PORT_5432_TCP_ADDR: <master IP address>
REP_USER: replication
REP_PASS: password
SLAVE_PORT_5432_TCP_ADDR: <slave IP address>
PGARCHIVE: "/var/lib/postgresql/archive"
#!/bin/bash -ve
echo "Creating pg_hba.conf..."
sed -e "s/\${REP_USER}/$REP_USER/" \
-e "s/\${DB_NAME}/$DB_NAME/" \
-e "s/\${DB_USER}/$DB_USER/" \
/tmp/postgresql/pg_hba.conf \
> $PGDATA/pg_hba.conf
echo "Creating pg_hba.conf complete."
echo "Creating postgresql.conf..."
cp /tmp/postgresql/postgresql.conf $PGDATA/postgresql.conf
echo "Creating replication user..."
gosu postgres postgres --single <<-EOSQL
CREATE ROLE ${REP_USER} ENCRYPTED PASSWORD '${REP_PASS}' REPLICATION LOGIN CONNECTION LIMIT 4;
EOSQL
echo "Creating replication user complete."
echo "Creating example database..."
gosu postgres postgres --single <<-EOSQL
CREATE DATABASE ${DB_NAME};
CREATE ROLE ${DB_USER} ENCRYPTED PASSWORD '${DB_PASSWORD}' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
GRANT ALL PRIVILEGES ON DATABASE ${DB_NAME} to ${DB_USER};
EOSQL
echo "Creating example database complete."
mkdir ${PGARCHIVE}
chown postgres:postgres ${PGARCHIVE}
chown -R postgres:postgres ${PGDATA}
#!/bin/bash -ve
echo "Creating .pgpass"
cat > ~/.pgpass <<EOS
${MASTER_PORT_5432_TCP_ADDR}:5432:replication:${REP_USER}:${REP_PASS}
EOS
chmod 0600 ~/.pgpass
echo "Cleaning up old cluster directory"
rm -rf ${PGDATA}/*
echo "Starting base backup as replicator"
pg_basebackup -h ${MASTER_PORT_5432_TCP_ADDR} -D ${PGDATA} -U ${REP_USER} -vPw --xlog-method=stream
echo "Creating pg_hba.conf..."
sed -e "s/\${REP_USER}/$REP_USER/" \
-e "s/\${DB_NAME}/$DB_NAME/" \
-e "s/\${DB_USER}/$DB_USER/" \
/tmp/postgresql/pg_hba.conf \
> $PGDATA/pg_hba.conf
echo "Creating pg_hba.conf complete."
echo "Creating postgresql.conf..."
cat > $PGDATA/postgresql.conf <<EOS
wal_level = hot_standby
hot_standby = on
max_standby_streaming_delay = 15min
EOS
echo "Writing recovery.conf file"
cat > ${PGDATA}/recovery.conf <<EOS
standby_mode = 'on'
primary_conninfo = 'host=${MASTER_PORT_5432_TCP_ADDR} port=5432 user=${REP_USER} password=${REP_PASS}'
trigger_file = '/tmp/promote_db_slave'
EOS
echo "Creating the staging directory to hold the master's log files"
mkdir ${PGARCHIVE}
chown postgres:postgres ${PGARCHIVE}
chown -R postgres:postgres ${PGDATA}
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /var/lib/postgresql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
postgres -D /var/lib/postgresql/data
or
pg_ctl -D /var/lib/postgresql/data -l logfile start
PostgreSQL stand-alone backend 9.3.6
backend> statement: ALTER USER "postgres" WITH SUPERUSER PASSWORD '*******' ;
backend>
Creating pg_hba.conf...
Creating pg_hba.conf complete.
Creating postgresql.conf...
Creating replication user...
PostgreSQL stand-alone backend 9.3.6
backend> backend> Creating replication user complete.
Creating example database...
PostgreSQL stand-alone backend 9.3.6
backend> backend> backend> backend> Creating example database complete.
PostgreSQL stand-alone backend 9.3.6
backend> statement: CREATE DATABASE template_postgis
backend> statement: UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis'
backend>
PostgreSQL stand-alone backend 9.3.6
backend> backend>
PostgreSQL stand-alone backend 9.3.6
backend> 1: addtosearchpath (typeid = 25, len = -1, typmod = -1, byval = f)
----
1: addtosearchpath = "topology has been added to end of database search_path " (typeid = 25, len = -1, typmod = -1, byval = f)
----
backend>
PostgreSQL stand-alone backend 9.3.6
LOG: database system was shut down at 2015-04-10 03:53:38 GMT
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /var/lib/postgresql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
postgres -D /var/lib/postgresql/data
or
pg_ctl -D /var/lib/postgresql/data -l logfile start
PostgreSQL stand-alone backend 9.3.6
backend> statement: ALTER USER "postgres" WITH SUPERUSER PASSWORD '*****' ;
backend>
Creating .pgpass
Cleaning up old cluster directory
Starting base backup as replicator
transaction log start point: 0/A000028 on timeline 1
pg_basebackup: starting background WAL receiver
0/36356 kB (0%), 0/1 tablespace (...lib/postgresql/data/backup_l36365/36365 kB (100%), 1/1 tablespace
transaction log end point: 0/A0000F0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
Creating pg_hba.conf...
Creating pg_hba.conf complete.
Creating postgresql.conf...
Writing recovery.conf file
Creating the staging directory to hold the master's log files
LOG: database system was interrupted; last known up at
LOG: entering standby mode
LOG: redo starts at 0/A000028
LOG: consistent recovery state reached at 0/A0000F0
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow anyone to connect remotely so long as they have a valid username and
# password.
host replication ${REP_USER} 0.0.0.0/0 md5
host ${DB_NAME} ${DB_USER} 0.0.0.0/0 md5
listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 10
max_connections=100
checkpoint_segments = 8
wal_keep_segments = 8
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment