postgresql cluster (patroni) with timescaledb.

[vitabaks]

Question:

Could you please send across, or the changes that i need to make on postgresql cluster to make it work for timescaledb.

Answer:

Everything is very simple! And I myself use timescaledb in production in this cluster scheme.

Please read the documentation to get started: https://docs.timescale.com/latest/getting-started/installation/

Example for: Installation on: Debian Install method: apt PostgreSQL version: 12

1. Add timescaledb repository to download packages (in the /vars/Debian.yml variable file):

apt_repository_keys:
  - key: "https://www.postgresql.org/media/keys/ACCC4CF8.asc" # postgresql repository apt key
  - key: "https://packagecloud.io/timescale/timescaledb/gpgkey" # timescaledb repository apt key

apt_repository:
  - repo: "deb http://apt.postgresql.org/pub/repos/apt/ {{ ansible_distribution_release }}-pgdg main" # postgresql apt repository
  - repo: "deb https://packagecloud.io/timescale/timescaledb/debian/ {{ ansible_distribution_release }} main" # timescaledb apt repository

2. Add timescaledb packages for automatic installation in the postgresql_packages variable (in the /vars/Debian.yml variable file):

postgresql_packages:
  - postgresql-{{ postgresql_version }}
  - postgresql-client-{{ postgresql_version }}
  - postgresql-server-dev-{{ postgresql_version }}
  - postgresql-contrib-{{ postgresql_version }}
  - timescaledb-2-postgresql-{{ postgresql_version }}

3. Add/edit the necessary PostgreSQL parameters for timescaledb (in the /vars/main.yml variable file):

postgresql_version: "12"

postgresql_users: # this is optional
postgresql_databases: # this is optional
postgresql_extensions:
  - {ext: "timescaledb", db: "postgres"} # or my database name

postgresql_parameters:
  - {option: "max_locks_per_transaction", value: "512"}
  - {option: "shared_preload_libraries", value: "timescaledb"}

Specify all other variables according to your personal requirements for the database cluster.

Deployment: quick start

[vitabaks]

timescaledb repository for RHEL/CentOS 7/8 (in the /vars/RedHat.yml variable file)

yum_repository:
  - name: "timescale_timescaledb"
    description: "timescaledb repo"
    baseurl: "https://packagecloud.io/timescale/timescaledb/el/{{ ansible_distribution_major_version }}/x86_64"
    gpgkey: "https://packagecloud.io/timescale/timescaledb/gpgkey"
    gpgcheck: "no"

[vitabaks]

timescaledb repository for Ubuntu (in the /vars/Debian.yml variable file):

apt_repository_keys:
  - key: "https://www.postgresql.org/media/keys/ACCC4CF8.asc" # postgresql repository apt key
  - key: "https://keyserver.ubuntu.com/pks/lookup?op=get&search=0x55ee6bf7698e3d58d72c0dd9ecb3980cc59e610b" # timescaledb PPA repository key

apt_repository:
  - repo: "deb http://apt.postgresql.org/pub/repos/apt/ {{ ansible_distribution_release }}-pgdg main" # postgresql apt repository
  - repo: "deb http://ppa.launchpad.net/timescale/timescaledb-ppa/ubuntu {{ ansible_distribution_release }} main" # timescaledb PPA repository

[vitabaks]

PostgreSQL 12 support added in TimescaleDB since version 1.7.0 (2020-04-16)

Deprecation Notice: Please note that with the release of Timescale 1.7, are deprecating support for PostgreSQL 9.6.x and 10.x.

[marcusteixeira]

Great Work! One thing we can put in the role, is when timescaledb_install is set to true, already running timescaledb-tune.

Ref: https://docs.timescale.com/latest/getting-started/configuring

[vitabaks]

@marcusteixeira Thanks for your feedback!

One thing we can put in the role, is when timescaledb_install is set to true, already running timescaledb-tune.

That is unnecessary. You can specify all the necessary parameters in the variable postgresql_parameters (ex. max_locks_per_transaction) in vars/main.yml variable file.

[valentin-fischer]

Hmmm....very interesting this TimescaleDB. I've been using influxdb for the last 2 years, maybe I can do a switch ...

Thanks!

[vitabaks]

PostgreSQL 13 support added in TimescaleDB since version 2.1.0 (2021-02-22)

[vitabaks]

PostgreSQL 11 is not supported with TimescaleDB 2.4.

[vitabaks]

PostgreSQL 14 support added in TimescaleDB since version 2.5.0 (2021-10-28)

[sojjan1337]

+1 for implementation

[vitabaks]

+1 for implementation

@sojjan1337 what do you mean by implementation?

After all, this has already been implemented, just specify the necessary variables as indicated above.

[sojjan1337]

Can i enable timescale from start in a configuration-file? The same way as you enable ha-proxy?

enable_timscale = True

Thanks!

[vitabaks]

@sojjan1337 ok, I'll look for time to implement this functionality

[sojjan1337]

Big thanks! I'm using this for a Zabbix 6 HA setup. Would be great to have a ansible-playbook to set this up with both zabbix and postgres. Have a nice evening. :)

[vitabaks]

PostgreSQL 15 support added in TimescaleDB since version 2.10.0 (2023-02-21)

[sojjan1337]

PostgreSQL 15 support added in TimescaleDB since version 2.10.0 (2023-02-21)

YoYo! Does this mean that when i run your latest ansible-playbook with postgres15, timescale is installed by default? Or maybe you have a "enable - timescale" feature now? I haven't looked for a while.

Big thanks man!

[vitabaks]

Does this mean that when i run your latest ansible-playbook with postgres15, timescale is installed by default?

Please see the instructions: https://github.com/vitabaks/postgresql_cluster/issues/31#issue-581160120

Or maybe you have a "enable - timescale" feature now? I haven't looked for a while.

TODO - https://github.com/vitabaks/postgresql_cluster/issues/235

[vitabaks]

Now, to deploy a PostgreSQL High-Availability Cluster (based on "Patroni") with the TimescaleDB extension, you just need to specify only one variable enable_timescale=true

Example:

ansible-playbook deploy_pgcluster.yml -e "enable_timescale=true"

[vitabaks]

PostgreSQL 12 is not supported starting with TimescaleDB 2.12.

[vitabaks]

PostgreSQL 16 support added in TimescaleDB since version 2.13.0 (2023-11-28)