{"id":10957,"date":"2024-05-08T20:24:02","date_gmt":"2024-05-09T01:24:02","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=10957"},"modified":"2024-05-21T20:41:01","modified_gmt":"2024-05-22T01:41:01","slug":"10957","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=10957","title":{"rendered":"Postgresql and Timescale with RedHat VDO"},"content":{"rendered":"

RedHat is phasing out ZFS \u2013 there are several reasons for this move, but primarily ZFS is a closed source Solaris (now Oracle) codebase. While OpenZFS exists, it’s not quite ‘the same’. RedHat’s preferred solution is Virtual Data Optimizer (VDO)<\/a>. This page walks through the process of installing PostgreSQL and creating a database cluster on VDO and installing TimescaleDB extension on the database cluster for RedHat Enterprise 8 (RHEL8)<\/p>\n

Before we create a VDO disk, we need to install it<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
yum <\/code>install<\/code> vdo kmod-kvdo<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

Then we need to create a vdo \u2013 here a VDO named ‘PGData’ is created on \/dev\/sdb\u00a0\u2013 a 9TB volume on which we will hold 16TB<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
vdo create --name=PGData --device=<\/code>\/dev\/sdb<\/code> --vdoLogicalSize=16T<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

Check to verify that the object was created\u00a0\u2013 it is \/dev\/mapper\/PGData in this instance<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
vdo list<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

Now format the volume using xfs.<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
mkfs.xfs <\/code>\/dev\/mapper\/PGData<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

And finally add a mount point<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
# Create the mount point folder<\/code><\/div>\n
mkdir<\/code> \/pgpool<\/code><\/div>\n
# Update fstab to mount the new volume to that mount pint<\/code><\/div>\n
cat<\/code> \/etc\/fstab<\/code><\/div>\n
\/dev\/mapper\/PGData<\/code> \/pgpool<\/code> xfs defaults,x-systemd.requires=vdo.service 0 0<\/code><\/div>\n
<\/div>\n
# Load the updated fstab<\/code><\/div>\n
systemctl daemon-reload<\/code><\/div>\n
<\/div>\n
# and mount the volume<\/code><\/div>\n
mount<\/code> -a<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

it should be mounted at ‘\/pgpool\/’<\/p>\n

The main reason for using VDO with Postgres is because of its compression feature\u00a0\u2013 this is automatically enabled, although we may need to tweak settings as we test it.<\/p>\n

We now have a place in our pool where we want our Postgres database to store its data. So let’s go ahead and install PostgreSQL,<\/p>\n

here we are using RHEL8 and installing PostgreSQL 12<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
# Install the repository RPM:<\/code><\/div>\n
dnf <\/code>install<\/code> -y https:<\/code>\/\/download<\/code>.postgresql.org<\/code>\/pub\/repos\/yum\/reporpms\/EL-8-x86_64\/pgdg-redhat-repo-latest<\/code>.noarch.rpm<\/code><\/div>\n
dnf clean all<\/code><\/div>\n
<\/div>\n
# Disable the built-in PostgreSQL module:<\/code><\/div>\n
dnf -qy module disable postgresql<\/code><\/div>\n
<\/div>\n
# Install PostgreSQL:<\/code><\/div>\n
dnf <\/code>install<\/code> -y postgresql12-server<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

Once the installation is done we need to initiate the database cluster and start the server . Since we want our Postgres to store data in our VDO volume we need to initialize it into our custom directory, we can do that in many ways,<\/p>\n

In all cases we need to make sure that the mount point of our zpool i.e., ‘\/pgpool\/pgdata\/’ is owned by the ‘postgres’ user which is created when we install PostgreSQL. We can do that by running the below command before running below steps for starting the postgres server<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
mkdir<\/code> \/pgpool\/pgdata<\/code><\/div>\n
chown<\/code> -R postgres:postgres <\/code>\/pgpool<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

Customize the systemd service by editing the postgresql-12 unit file and updateding the PGDATA environment variable<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
vdotest-uos:pgpool <\/code># grep Environment \/usr\/lib\/systemd\/system\/postgresql-12.service<\/code><\/div>\n
# Note: avoid inserting whitespace in these Environment= lines, or you may<\/code><\/div>\n
Environment=PGDATA=<\/code>\/pgpool\/pgdata<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

and\u00a0 then initialize, enable and start our server as below<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
\/usr\/pgsql-12\/bin\/postgresql-12-setup<\/code> initdb<\/code><\/div>\n
systemctl <\/code>enable<\/code> postgresql-12<\/code><\/div>\n
systemctl start postgresql-12<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

Here ‘\/usr\/pgsql-12\/bin\/’ is the bin directory of postgres installation you can substitute it with your bin directory path.<\/p>\n

or<\/p>\n

We can also directly give the data directory value while initializing db using below command<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
\/usr\/pgsql-12\/bin\/initdb<\/code> -D <\/code>\/pgpool\/pgdata\/<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

and then start the server using<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
systemctl start postgresql-12<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

Now we have installed postgreSQL and started the server, we will install the Timescale extension for Postgres now.<\/p>\n

add the time scale repo with below command<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
tee<\/code> \/etc\/yum<\/code>.repos.d<\/code>\/timescale_timescaledb<\/code>.repo <<EOL<\/code><\/div>\n
[timescale_timescaledb]<\/code><\/div>\n
name=timescale_timescaledb<\/code><\/div>\n
baseurl=https:<\/code>\/\/packagecloud<\/code>.io<\/code>\/timescale\/timescaledb\/el\/8\/<\/code>\\$basearch<\/code><\/div>\n
repo_gpgcheck=1<\/code><\/div>\n
gpgcheck=0<\/code><\/div>\n
enabled=1<\/code><\/div>\n
gpgkey=https:<\/code>\/\/packagecloud<\/code>.io<\/code>\/timescale\/timescaledb\/gpgkey<\/code><\/div>\n
sslverify=1<\/code><\/div>\n
sslcacert=<\/code>\/etc\/pki\/tls\/certs\/ca-bundle<\/code>.crt<\/code><\/div>\n
metadata_expire=300<\/code><\/div>\n
EOL<\/code><\/div>\n
sudo<\/code> yum update -y<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

then install\u00a0 it using below command<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
yum <\/code>install<\/code> -y timescaledb-postgresql-12<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

After installing we need to add ‘timescale’ to shared_preload_libraries<\/strong> in our postgresql.conf<\/strong>, Timescale gives us ‘timescaledb-tune<\/strong>‘ which can be used for this and also configuring different settings for our database. Since we initialize our PG database cluster in a custom location we need to point the direction of postgresql.conf to timescaledb-tune it also requires a path to our pg_config file we can do both by following command.<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
timescaledb-tune --pg-config=<\/code>\/usr\/pgsql-12\/bin\/pg_config<\/code> --conf-path=<\/code>\/pgpool\/pgdata\/postgresql<\/code>.conf<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

After running above command we need to restart our Postgres server, we can do that by one of the below commands<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
systemctl restart postgresql-12<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

After restarting using one of the above commands connect to the database you want to use Timescale hypertables in and run below statement to load Timescale extension<\/p>\n

\n
\n
\n
\n\n\n\n
\n
\n
CREATE<\/code> EXTENSION IF <\/code>NOT<\/code> EXISTS timescaledb <\/code>CASCADE<\/code>;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

you can check if Timescale is loaded by passing ‘\\dx’ command to psql which will load the extension list.<\/p>\n

in order to configure PostgreSQL to allow remote connection we need to do couple of changes as below<\/p>\n","protected":false},"excerpt":{"rendered":"

RedHat is phasing out ZFS \u2013 there are several reasons for this move, but primarily ZFS is a closed source Solaris (now Oracle) codebase. While OpenZFS exists, it’s not quite ‘the same’. RedHat’s preferred solution is Virtual Data Optimizer (VDO). This page walks through the process of installing PostgreSQL and creating a database cluster on …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1522,30],"tags":[294,1349,2011,1195,2010,2012,2013,2014],"class_list":["post-10957","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-system-administration","tag-linux","tag-postgresql","tag-postgresql12","tag-redhat","tag-rhel8","tag-timescale","tag-vdo","tag-virtual-data-optimizer"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/10957","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10957"}],"version-history":[{"count":3,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/10957\/revisions"}],"predecessor-version":[{"id":10960,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/10957\/revisions\/10960"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10957"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10957"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10957"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}