{"id":104,"date":"2019-06-02T00:10:52","date_gmt":"2019-06-01T23:10:52","guid":{"rendered":"https:\/\/blog.inplico.uk\/?p=104"},"modified":"2024-07-29T17:54:29","modified_gmt":"2024-07-29T16:54:29","slug":"postgresql","status":"publish","type":"post","link":"https:\/\/blog.inplico.uk\/?p=104","title":{"rendered":"Postgresql"},"content":{"rendered":"

As it does; Debian will bastardise the installation of postgresql in the usual manner, but it does offer a reasonable way to remove the bastardisation and configure it yourself so that you can have the data directory exactly where you want it.<\/p>\n

The idea behind having a separate logical volume for the data is that if the worst comes to the worst and for some reason you have to reinstall the operating system, the data will still be there when the operation is complete. If you have partitioned the volumes according to the previous section then you will need to follow this guide in order to make use of the partitions that you created.<\/p>\n

As always we start with the install<\/p>\n

apt-get install postgresql postgresql-common postgresql-client<\/pre>\n
The installation process automatically creates the database, creates a user and group called \u201cpostgres\u201d and starts the postgresql service. As we want to reconfigure it, the first thing we need to do is stop the service using our favorite method (be it systemctl or service):<\/p>\n
service postgresql stop<\/pre>\n
If you like you can check that the service is indeed stopped with \u201cservice postgresql status\u201d. The next thing that we need to do is get rid of the database and cluster that the installation process created. Please note that this step is mandatory as it will not be possible to create the new cluster until the old one has been deleted.<\/p>\n
At the time of updating the latest version of postgresql on Debian is 15. If you are running a different version then you will need to change where it says \u201c9.4\u201d in this documentation with whatever version you are installing. to get rid of the old data run<\/p>\n
pg_dropcluster 15 main<\/pre>\n
Once the old cluster has gone then you can create a new one. To do this simply run<\/p>\n
pg_createcluster -d \/pgsql\/15 15 main<\/pre>\n
This will create the new cluster in the correct directory. Note that postgresql does not like its database being directly installed on a mount point which is one reason why we use the .\/15 directory. The second being that it makes upgrading easier.<\/p>\n
Note that pg_createcluster will set the correct privileges and the correct place for the log file (\/var\/log\/postgresql) automatically if you run it as root.<\/p>\n
At this point you should be able to start the service<\/p>\n
service postgresql start<\/pre>\n
If it takes a long time to start then it probably hasn\u2019t. The big problem here is that when you run \u201cservice postgresql status\u201d it will likely say that it is active, but when you try to connect with psql you will get a message that says<\/p>\n
sql: could not connect to server: No such file or directory\r\nIs the server running locally and accepting\r\nconnections on Unix domain socket \"\/var\/run\/postgresql\/.s.PGSQL.5432\"?<\/pre>\n
Note that you will not actually be able to connect to the database at this stage because of permissions in pg_hba.conf, but you should at least get the message<\/p>\n
psql: FATAL: Peer authentication failed for user \"postgres\"<\/pre>\n
rather than the previous message.<\/p>\n
If for some reason it goes tits up then try reconfiguring both postgresql and postgresql-common.<\/p>\n
dpkg-reconfigure postgresql\r\ndpkg-reconfigure postgresql-common<\/pre>\n
In the case of postgresql-common you may be asked if wish to enable ssl by default in new clusters. Select \u201cyes\u201d for this option; albeit we now configure ssl manually so we can use it for replication.<\/p>\n
One final action is to edit the pg_hba.conf file to trust the user postgres locally.<\/p>\n
vi \/etc\/postgresql\/15\/main\/pg_hba.conf<\/pre>\n
Peer authentication works be reading the logged on user so if your pg_hba.conf file has an entry like this<\/p>\n
local   all             postgres                                peer<\/pre>\n
then you will need to change to the user “postgres” before you can run psql<\/p>\n
$su postgres\r\n$psql<\/pre>\n
You can change it to “trust” if you wish however there re security implications for this so it is not recommended<\/strong><\/p>\n
local   all             postgres                                trust<\/pre>\n
Once you have done this, restart the postgresql service again and you should be able to connect to the server using<\/p>\n
psql \u2013U postgres<\/pre>\n
That\u2019s about it for the postgresql install however you will have to configure the authentication before you can make much use of it.<\/p>\n
 <\/p>\n
Addendum:<\/strong><\/h4>\n
SERVICE FAILS ON REBOOT (NAS Storage)<\/strong><\/h3>\n
If you have set up your cluster on a nas of some description or other and are mounting it in fstab with nfs, sshfs, etc. then there is a possibility that the postgresql service will fail on start up but start if your run it manually.\u00a0 This is because it is trying to start before the mount is available.\u00a0 To resolve this you need to edit the service file (which at the time of this update I was using postgresql 11 so the service file was:<\/p>\n
 \/run\/systemd\/generator\/postgresql.service.wants\/postgresql@11-main.service<\/pre>\n
if you look in the file you will note a declaration RequiresMountsFor<\/strong> followed by a space separated list.\u00a0 If you append this file with the mountpoint where you have created the cluster and reboot then everything should work.<\/p>\n
RequiresMountsFor=\/etc\/postgresql\/%I \/var\/lib\/postgresql\/%I \/pgsql<\/pre>\n
Note:\u00a0 The “%I” is explained within the file, it is not necessary to append this to the nas mount.\u00a0 Just remember that if you update postgresql you will probably have update this manually.<\/p>\n
Once rebooted type systemctl<\/strong> and review the services to make sure they all started, and to make doubly sure log in to a database using \/psql.<\/p>\n
 <\/p>\n
AUTHENTICATION<\/strong><\/h3>\n
As of version 15 postgresql now defaults to sha-256 but it is still worth checking that this is the case.\u00a0 Open the postgresql.conf<\/strong> file in your favourite editor<\/p>\n
vim \/etc\/postgresql\/13\/main\/postgresql.conf<\/pre>\n
and find the password_encryption<\/strong> directive<\/p>\n
#password_encryption = md5             # md5 or scram-sha-256<\/pre>\n
If the line is commented out then uncomment it and change the value to scram-sha-256<\/strong><\/p>\n
Once you have done that change “md5<\/strong>” to “scram-sha-256<\/strong>” in \/etc\/postgresql\/15\/main\/pg_hba.conf <\/strong>and restart postgresql<\/p>\n
If you already have passwords set then you will need to re-enter or change them.\u00a0 The easiest way to do this when you are changing a few is to use psql<\/strong><\/p>\n
$su postgres\r\n$psql<\/pre>\n
and then issue the \\password command and follow the instructions on screen<\/p>\n
\\password postfixro<\/pre>\n
to change the “postfixro” user’s password for example.<\/p>\n
 <\/p>\n
UPGRADING POSTGRESQL\u00a0 (Debian servers)<\/strong><\/h3>\n
If you have followed these instructions and have your database in a non default location then upgrading it is slightly different to most instructions on the internet.\u00a0 These instructions are for upgrading from 11 to 13 but should work equally well for other versions.<\/p>\n
First thing to do is backup the database.\u00a0 First thing to do is create a directory to store the backup (this must be empty)<\/p>\n
mkdir ~\/pgbackup<\/pre>\n
Now run pg_basebackup to backup the existing database<\/p>\n
pg_basebackup -h localhost -U replicate -P -D ~\/pgbackup -F t<\/pre>\n
The previous command assumes you have set up a user called “replicate<\/strong>“.\u00a0 If not then use a username that has sufficient permissions to perform the backup.<\/p>\n
You will then be prompted for a password.<\/p>\n
Now run “pg_lsclusters<\/strong>“.\u00a0 This will list all clusters and their locations.<\/p>\n
Ver Cluster Port Status Owner    Data directory              Log file\r\n11  main    5432 online postgres \/pgsql\/data                 \/var\/log\/postgresql\/postgresql-11-main.log\r\n13  main    5433 online postgres \/var\/lib\/postgresql\/13\/main \/var\/log\/postgresql\/postgresql-13-main.log\r\n<\/pre>\n
You will note that we have 2 clusters (a version 11 one and a version 13 one).\u00a0 If you check the data directory you will see that the one that we are using is “\/pgsql\/data<\/strong>“.\u00a0 The version 13 one is in the standard Debian location.\u00a0 We need to get rid of that one before we go any furhter.<\/p>\n
Next thing to do is stop postgresql and then we can drop the version 13 database with the command:<\/p>\n
pg_dropcluster 15 main<\/pre>\n
Once we have rid of the default version 15 cluster we can create one of our own but first we need to find somewhere to put it so we need to make a new clean directory in our \/pgsql<\/strong> folder.<\/p>\n
mkdir \/pgsql\/15<\/pre>\n
Now we can upgrade our version 13 cluster to version 15.<\/p>\n
sudo -u postgres pg_upgradecluster -v 15 13 main \/pgsql\/15<\/pre>\n
This can take some time if the databases are large but should complete eventually.<\/p>\n
To check that the upgraded cluster exists you can use pg_lsclusters and also have a look in \/pgsql\/15 to see if the cluster directory structure is there.\u00a0 This time pg_lsclusters should show something like<\/p>\n
Ver Cluster Port Status Owner    Data directory Log file\r\n11  main    5433 down   postgres \/pgsql\/13      \/var\/log\/postgresql\/postgresql-%Y-%m-%d_%H%M%S.log\r\n13  main    5432 down   postgres \/pgsql\/15      \/var\/log\/postgresql\/postgresql-%Y-%m-%d_%H%M%S.log\r\n<\/pre>\n
One of the important things to check here is the port.\u00a0 The version 15 needs to be running on port 5432<\/strong> or you will have to reconfigure all the services that use your databases.\u00a0 The port is set in \/etc\/postgresql\/15\/main\/postgresql.conf<\/strong><\/p>\n
You will note at this point that both clusters are marked as down.\u00a0 If you start the postgresql service now then it will appear as though it is running however if you try and connect with psql then it will fail.\u00a0 We can however start the cluster manually and connect to test by issuing the following command:<\/p>\n
pg_ctlcluster 15 main start<\/pre>\n
Now if you try psql then it should connect, as should your application.<\/p>\n
To check that the cluster is live run pg_lsclusters<\/strong> again and it should indicate version 13 is online<\/p>\n
11  main    5433 down   postgres \/pgsql\/data    \/var\/log\/postgresql\/postgresql-%Y-%m-%d_%H%M%S.log\r\n13  main    5432 online postgres \/pgsql\/13      \/var\/log\/postgresql\/postgresql-%Y-%m-%d_%H%M%S.log\r\n<\/pre>\n
If you reboot or restart postgresql now then it will fail again as we need to tell systemd that we want to start the cluster automatically.\u00a0 To do so simply run<\/p>\n
systemctl enable postgresql@15<\/pre>\n
We also need to edit \/etc\/postgresql\/15\/main\/start.conf to change “manual<\/strong>” to “auto<\/strong>”<\/p>\n
# This cluster was upgraded to a newer major version. The old\r\n# cluster has been preserved for backup purposes, but is not started\r\n# automatically.\r\n\r\nauto\r\n<\/pre>\n
Now if we restart postgresql in the ordinary manner then it should work fine.<\/p>\n
If all is well you can disable and drop the old cluster (albeit I normally like to keep it around for a few weeks just to make sure nothing has gone too wrong during the upgrade.\u00a0 When you want to get rid of it just run<\/p>\n
systemctl disable postgresql@13\r\npg_dropcluster 13 main<\/pre>\n
This should complete the upgrade steps.<\/p>\n
As a final check if you go to the postgresql command prompt and issue the following command it should tell you that you are running version 15:<\/p>\n
SELECT VERSION();<\/pre>\n
psql -U postgres\r\npsql (15.7 (Debian 15.7-0+deb12u1))\r\nType \"help\" for help.\r\n\r\npostgres=# SELECT VERSION();\r\n                                                           version                                                           \r\n-----------------------------------------------------------------------------------------------------------------------------\r\n PostgreSQL 15.7 (Debian 15.7-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit\r\n(1 row)\r\n\r\npostgres=# \r\n<\/pre>\n
This upgrade process should work find for other versions, you simply need to change the version numbers.<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
As it does; Debian will bastardise the installation of postgresql in the usual manner, but it does offer a reasonable way to remove the bastardisation and configure it yourself so that you can have the data directory exactly where you want it. The idea behind having a separate logical volume for the data is that […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10,4],"tags":[],"class_list":["post-104","post","type-post","status-publish","format-standard","hentry","category-debian-server","category-a-series-of-hints-and-tips-when-using-the-postgresql-library-libpq-fe-h"],"_links":{"self":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts\/104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=104"}],"version-history":[{"count":19,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts\/104\/revisions"}],"predecessor-version":[{"id":515,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts\/104\/revisions\/515"}],"wp:attachment":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}