]> git.immae.eu Git - github/fretlink/ansible-postgresql-role.git/log
github/fretlink/ansible-postgresql-role.git
4 years agotests: remove automatic tests with PG 9.5 & 9.6
Paul B [Mon, 11 May 2020 15:34:21 +0000 (17:34 +0200)] 
tests: remove automatic tests with PG 9.5 & 9.6

I have been having troubles to get the tests to pass with PG version
9.5 and 9.6.

Not sure why but when we try to start the secondaries (after the whole
cluster setup) they don't want to start with the following logs:

```
Starting PostgreSQL 9.6 database server: main
The PostgreSQL server failed to start. Please check the log output:
 2020-05-11 13:18:11.883 UTC [6403] LOG: database system was shut down at 2020-05-11 13:16:41 UTC
 ssh: connect to host postgres_barman port 22: Connection refused
 ERROR: The required file is not available: 00000002.history
 2020-05-11 13:18:12.236 UTC [6403] LOG: entering standby mode
 2020-05-11 13:18:12.270 UTC [6409] [unknown]@[unknown] LOG: incomplete startup packet
 ssh: connect to host postgres_barman port 22: Connection refused
 ERROR: The required file is not available: 000000010000000000000001
 2020-05-11 13:18:12.561 UTC [6403] WARNING: WAL was generated with wal_level=minimal, data may be missing
 2020-05-11 13:18:12.561 UTC [6403] HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup.
 2020-05-11 13:18:12.561 UTC [6403] FATAL: hot standby is not possible because wal_level was not set to \"replica\" or higher on the master server
 2020-05-11 13:18:12.561 UTC [6403] HINT: Either set wal_level to \"replica\" on the master, or turn off hot_standby here.
 2020-05-11 13:18:12.563 UTC [6402] LOG: startup process (PID 6403) exited with exit code 1
 2020-05-11 13:18:12.563 UTC [6402] LOG: aborting startup due to startup process failure
 2020-05-11 13:18:12.576 UTC [6402] LOG: database system is shut down ... failed!
 failed!
```

The fatal error being:

```
 2020-05-11 13:18:12.561 UTC [6403] FATAL: hot standby is not possible because wal_level was not set to \"replica\" or higher on the master server
```

even if the cluster has been started with `logical` wal level from the
start.

It works with later version of PG 10+ so I can leave without those
versions being tested for now.

P.S.: for the sake of comparaison, here are the starting logs of the
secondaries with PG10 (the database starts accepting connections even
with the errors):

```
2020-05-11 15:45:52.640 UTC [8392] LOG:  listening on IPv4 address "172.17.0.4", port 5432
2020-05-11 15:45:52.657 UTC [8392] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-05-11 15:45:52.806 UTC [8393] LOG:  database system was shut down at 2020-05-11 15:44:10 UTC
ssh: connect to host postgres_barman port 22: Connection refused
ERROR: The required file is not available: 00000002.history
2020-05-11 15:45:53.226 UTC [8393] LOG:  entering standby mode
ssh: connect to host postgres_barman port 22: Connection refused
ERROR: The required file is not available: 000000010000000000000001
2020-05-11 15:45:53.577 UTC [8393] LOG:  consistent recovery state reached at 0/1632D20
2020-05-11 15:45:53.577 UTC [8393] LOG:  invalid record length at 0/1632D20: wanted 24, got 0
2020-05-11 15:45:53.578 UTC [8392] LOG:  database system is ready to accept read only connections
2020-05-11 15:45:53.598 UTC [8403] FATAL:  could not connect to the primary server: FATAL:  password authentication failed for user "replicator"
ssh: connect to host postgres_barman port 22: Connection refused
ERROR: The required file is not available: 00000002.history
2020-05-11 15:45:54.122 UTC [8409] [unknown]@[unknown] LOG: incomplete startup packet
```

4 years agoparameters: allow wal_level customisation
Paul B [Mon, 11 May 2020 13:10:33 +0000 (15:10 +0200)] 
parameters: allow wal_level customisation

This commit allows customisation of the `wal_level` PG config on all
supported PG versions

4 years agotests: running tests on all supported PG versions
Paul B [Wed, 6 May 2020 15:04:20 +0000 (17:04 +0200)] 
tests: running tests on all supported PG versions

4 years agocore: Add compatibility with PG 12 major version
Paul B [Wed, 6 May 2020 11:51:59 +0000 (13:51 +0200)] 
core: Add compatibility with PG 12 major version

This PR adds compatibility for postgresql 12 major version.

The PG12 configuration is as close as the PG11 one. All defaults have
been kept back to avoid breaking backward compatibility within this
ansible role.

Here is a complete side-by-side diff between the current PG11 conf and
the new PG12 conf:

```diff

                                        # 0 selects the syste # 0 selects the syste
                                                              > #tcp_user_timeout = 0 # TCP_USER_TIMEOUT, i
                                                              > # 0 selects the syste

                                                              > #ssl_min_protocol_version = 'TLSv1'
                                                              > #ssl_max_protocol_version = ''

                                                              > #shared_memory_type = mmap # the default is the
                                                              > # supported by the op
                                                              > #   mmap
                                                              > #   sysv
                                                              > #   windows
                                                              > # (change requires re
dynamic_shared_memory_type = posix # the default is the    dynamic_shared_memory_type = posix # the default is the
                                        # supported by the op # supported by the op
                                        #   posix #   posix
                                        #   sysv #   sysv
                                        #   windows #   windows
                                        #   mmap #   mmap
                                        # use none to disable <
                                        # (change requires re # (change requires re

wal_level = logical # minimal, replica, o | wal_level = {{ postgres_wal_level }} # min
                                        # (change requires re # (change requires re
wal_log_hints = on # also do full page w wal_log_hints = on # also do full page w
                                        # (change requires re # (change requires re
                                                              > #wal_init_zero = on # zero-fill new WAL f
                                                              > #wal_recycle = on # recycle WAL files
                                        # (change requires re # (change requires re

                                                              > # - Archive Recovery -
                                                              >
                                                              > # These are only used in recovery mode.
                                                              >
                                                              > {% if postgres_primary %}
                                                              > {# In PG < 12 versions all the recovery settings were in a se
                                                              > restore_command = '/usr/bin/barman-wal-restore --user barman
                                                              > # placeholders: %p = path of
                                                              > #               %f = file nam
                                                              > # e.g. 'cp /mnt/server/archiv
                                                              > # (change requires restart)
                                                              > {% else %}
                                                              > #restore_command = '' # command to use to restore a
                                                              > # placeholders: %p = path of
                                                              > #               %f = file nam
                                                              > # e.g. 'cp /mnt/server/archiv
                                                              > # (change requires restart)
                                                              > {% endif %}
                                                              > #archive_cleanup_command = '' # command to execute at every
                                                              > #recovery_end_command = '' # command to execute at compl
                                                              >
                                                              > # - Recovery Target -
                                                              >
                                                              > # Set these only when performing a targeted recovery.
                                                              >
                                                              > #recovery_target = '' # 'immediate' to end recovery
                                                              >                                 # consistent state is reached
                                                              > # (change requires restart)
                                                              > #recovery_target_name = '' # the named restore point to
                                                              > # (change requires restart)
                                                              > #recovery_target_time = '' # the time stamp up to which
                                                              > # (change requires restart)
                                                              > #recovery_target_xid = '' # the transaction ID up to wh
                                                              > # (change requires restart)
                                                              > #recovery_target_lsn = '' # the WAL LSN up to which rec
                                                              > # (change requires restart)
                                                              > #recovery_target_inclusive = on # Specifies whether to stop:
                                                              > # just after the specified re
                                                              > # just before the recovery ta
                                                              > # (change requires restart)
                                                              > {% if postgres_primary %}
                                                              > {# In PG < 12 versions all the recovery settings were in a se
                                                              > recovery_target_timeline='latest' # 'current', 'latest'
                                                              > # (change requires restart)
                                                              > {% else %}
                                                              > #recovery_target_timeline = 'latest' # 'current', 'latest'
                                                              > # (change requires restart)
                                                              > {% endif %}
                                                              > #recovery_target_action = 'pause' # 'pause', 'promote',
                                                              > # (change requires restart)

max_wal_senders = 10 # max number of walsender pro | #max_wal_senders = 10 # max number of walsender pro
                                # (change requires restart) # (change requires restart)

                                                              > {% if postgres_primary %}
                                                              > {# In PG < 12 versions this setting was defined in separate r
                                                              > primary_conninfo = 'host={{ postgres_primary.host }} port={{
                                                              > # (change requires re
                                                              > {% else %}
                                                              > #primary_conninfo = '' # connection string t
                                                              > # (change requires re
                                                              > {% endif %}
                                                              > #primary_slot_name = '' # replication slot on
                                                              > # (change requires re
                                                              > {% if postgres_primary %}
                                                              > {# In PG < 12 versions this setting was defined in separate r
                                                              > promote_trigger_file = '/var/lib/postgresql/{{ postgres_versi
                                                              > {% else %}
                                                              > #promote_trigger_file = '' # file name whose pre
                                                              > {% endif %}

                                        # retrieve WAL after                                            # retrieve WAL after
                                                              > #recovery_min_apply_delay = 0 # minimum delay for a

                                                              > #plan_cache_mode = auto # auto, force_generic
                                                              > # force_custom_plan
                                                              >

                                        #   debug5       <
                                        #   debug4       <
                                        #   debug3       <
                                        #   debug2       <
                                        #   debug1       <
                                        #   log       <
                                        #   notice       <
                                        #   warning       <
                                        #   error       <
                                                              <
log_min_duration_statement = 10000 # -1 is disabled, 0 l log_min_duration_statement = 10000 # -1 is disabled, 0 l
                                        # and their durations # and their durations
                                        # statements running                                            # statements running
                                        # of milliseconds # of milliseconds

                                                              > #log_transaction_sample_rate = 0.0 # Fraction of transac
                                                              > # are logged regardle
                                                              > # statements from all

                                                              > #client_min_messages = notice # values in order of
                                                              > #   debug5
                                                              > #   debug4
                                                              > #   debug3
                                                              > #   debug2
                                                              > #   debug1
                                                              > #   log
                                                              > #   notice
                                                              > #   warning
                                                              > #   error

                                        # only default tables # only default tables
                                                              > #default_table_access_method = 'heap'

                                                              > # selects precise out

                                                              > #data_sync_retry = off # retry or panic on f
                                                              > # data?
                                                              > # (change requires re

                                                              > # assignments, so they can usefully be given more than once.
```

5 years agoMerge pull request #8 from chtitux/barman-url 1.5.0
Paul B [Fri, 3 May 2019 12:27:18 +0000 (14:27 +0200)] 
Merge pull request #8 from chtitux/barman-url

Allow to set options for barman connectivity

5 years agoAllow to set options for barman connectivity
Théophile Helleboid [Thu, 2 May 2019 15:24:20 +0000 (17:24 +0200)] 
Allow to set options for barman connectivity

- Allow to pass arbitrary options
- Build the URL in a dedicated step
- Allow to specify path prefix for barman files
- Add documentation in [README.md](README.md)

5 years agoMerge pull request #9 from chtitux/become-method
Paul B [Fri, 3 May 2019 08:59:19 +0000 (10:59 +0200)] 
Merge pull request #9 from chtitux/become-method

Allow to use any ansible become method

5 years agoAllow to use any ansible become method
Théophile Helleboid [Thu, 2 May 2019 15:34:44 +0000 (17:34 +0200)] 
Allow to use any ansible become method

The variable can be set to 'sudo' if ansible uses sudo

5 years agoMerge pull request #6 from paulRbr/pg-extensions 1.4.0
Paul B [Tue, 29 Jan 2019 14:45:25 +0000 (15:45 +0100)] 
Merge pull request #6 from paulRbr/pg-extensions

feat(extensions): adds creation of extension on databases if needed

5 years agodoc: update README to document extension creation
Paul B [Tue, 29 Jan 2019 14:00:48 +0000 (15:00 +0100)] 
doc: update README to document extension creation

5 years agotests: fix test playbook due to barman <-> postgres circle dependencies
Paul B [Mon, 28 Jan 2019 15:03:15 +0000 (16:03 +0100)] 
tests: fix test playbook due to barman <-> postgres circle dependencies

5 years agotests: adding a database creation with extensions
Paul B [Fri, 25 Jan 2019 12:38:57 +0000 (13:38 +0100)] 
tests: adding a database creation with extensions

5 years agofeat(extensions): adds creation of extension on databases if needed
Paul B [Fri, 25 Jan 2019 10:55:41 +0000 (11:55 +0100)] 
feat(extensions): adds creation of extension on databases if needed

5 years agoMerge pull request #4 from paulRbr/better-loop 1.3.0
Théophile Helleboid - chtitux [Sun, 13 Jan 2019 12:02:05 +0000 (13:02 +0100)] 
Merge pull request #4 from paulRbr/better-loop

improvement: remove 'with_items' loop and use modern ansible loops

5 years agoimprovement: remove 'with_items' loop and use modern ansible loops
Paul B [Mon, 7 Jan 2019 17:01:00 +0000 (18:01 +0100)] 
improvement: remove 'with_items' loop and use modern ansible loops

Ansible > 2.5 needed

5 years agoMerge pull request #3 from paulRbr/barman-rsync-optional 1.2.0
Théophile Helleboid - chtitux [Wed, 2 Jan 2019 13:23:36 +0000 (14:23 +0100)] 
Merge pull request #3 from paulRbr/barman-rsync-optional

standby: rsync from barman is optional if SSH access is already here

5 years agostandby: rsync from barman is optional if SSH access is already here
Paul B [Fri, 28 Dec 2018 14:30:13 +0000 (15:30 +0100)] 
standby: rsync from barman is optional if SSH access is already here

6 years agoUpdate README.md with links to Galaxy 1.1.0
Théophile Helleboid [Fri, 9 Nov 2018 15:47:09 +0000 (16:47 +0100)] 
Update README.md with links to Galaxy

6 years agoMerge pull request #2 from chtitux/fix-ci
Théophile Helleboid - chtitux [Fri, 9 Nov 2018 15:35:20 +0000 (16:35 +0100)] 
Merge pull request #2 from chtitux/fix-ci

Fix Travis CI

6 years agoMerge pull request #1 from chtitux/postgres-11
Théophile Helleboid - chtitux [Fri, 9 Nov 2018 15:35:02 +0000 (16:35 +0100)] 
Merge pull request #1 from chtitux/postgres-11

Add PostgreSQL 11

6 years agoFix CI
Théophile Helleboid [Thu, 8 Nov 2018 22:52:14 +0000 (23:52 +0100)] 
Fix CI

6 years agoUpdate README
Théophile Helleboid [Thu, 8 Nov 2018 22:53:30 +0000 (23:53 +0100)] 
Update README

fix barman and postgres galaxy names

6 years agoAdd .gitignore
Théophile Helleboid [Thu, 8 Nov 2018 22:53:10 +0000 (23:53 +0100)] 
Add .gitignore

6 years agoAdd PostgreSQL 11
Théophile Helleboid [Thu, 8 Nov 2018 16:21:55 +0000 (17:21 +0100)] 
Add PostgreSQL 11

6 years agoInitial commit open sourcing Postgresql Ansible role 1.0.0
Paul B [Fri, 31 Aug 2018 09:49:09 +0000 (11:49 +0200)] 
Initial commit open sourcing Postgresql Ansible role