This role has been designed to:
- Install PostgreSQL on Debian.
- Optionally create a database and a user.
- Write the users password to the PostgreSQL superuser
$HOME/.pgpassfile when a user is added. - Create a cron job to dump all databases on a nightly basis.
To create multiple users and databases include this role once for each users / database required.
This role can be included using the tasks from the tasks/pgpass_read.yml file in order to read a users password.
This role requires JC version 1.22.3 to be installed on the Ansible controller as the PostgreSQL password file parser is used to read passwords from /var/lib/postgresql/.pgpass and the ini file parser is used to parse the postgresql.conf file.
See the defaults/main.yml file and the meta/argument_specs.yml file.
A boolean that defaults to false, set postgresql to true for the tasks in this role to be run.
The path for the nightly database backups to be written to, postgresql_backups defaults to /var/backups/postgres.
If this variable is empty then the database backups cron job will not be created.
The hour during which the database backups will be created, the minute is set randomly and saved and read from the PostgreSQL superuser $HOME/.cron_min file.
If postgresql_cron_hour is empty then the cron job will not be created.
The name of a database to be created, by default postgresql_db is not defined.
The encoding of the database to be created, for example, UTF8, by default postgresql_db_encoding is not defined.
The database collation order (LC_COLLATE), by default postgresql_db_lc_collate is not defined.
The database character classification (LC_CTYPE), by default postgresql_db_lc_ctype is not defined.
The database locale, if postgresql_db_locale is defined and postgresql_db_lc_collate and postgresql_db_lc_ctype are not then the value of postgresql_db_locale will be used for both when creating a database, for example C.
The, optional, database owner, if not set it will default to postgres.
The state of the database, see the module parameter, if not set postgresql_db_state defaults to present.
The PostgreSQL host, this variables is required and it defaults to localhost.
The initial PostgreSQL database (which is also called as maintenance DB) that Ansible connects to, it defaults to postgres.
A list of Debian packages to be installed by this role.
The port PostgreSQL uses, postgresql_port defaults to 5432, if postgresql_port is set to a value that differs from the port PostgreSQL is using then this role will update the port PostgreSQL uses.
The PostgreSQL super account, this is set to postgres by Debian and probably can't be changed.
The name of a PostgreSQL user account to create, postgresql_user is not defined by default.
The database privileges that the postgresql_user should have on the postgresql_db, if this variable is not set then ALL will be used when adding a database and user.
A boolean, validate variabels that start with postgresql_ using the meta/argument_specs.yml file.
After upgrading Debian PostgreSQL needs to be upgraded, for example when going from Bullseye to Bookworm:
pg_dropcluster --stop 15 main
pg_upgradecluster 13 main
apt remove postgresql-13 postgresql-client-13And Buster to Bullseye:
pg_dropcluster --stop 13 main
pg_upgradecluster 11 main
apt remove postgresql-11 postgresql-client-11The upgrade will also change the port number that PostgreSQL runs on so that both the previous version and the new version can run at the same time, this role can be used to revert the port number after the upgrade.
- The Debian PostgreSQL wiki page.
- The Ansible community.postgresql modules.
- The JC pgpass parser
The primary URL of this repo is https://git.coop/webarch/postgresql however it is also mirrored to GitHub and available via Ansible Galaxy.
If you use this role please use a tagged release, see the release notes.
Copyright 2020-2025 Chris Croome, <[email protected]>.
This role is released under the same terms as Ansible itself, the GNU GPLv3.