PostgreSQL

My solution for copying backups around the homelab

Leave a Comment / PostgreSQL, ssh / By /

I have database servers outside the homelab, as in not in my basement. They are in datacenters. I don’t let them push the backups into the basement. Instead, I let them call home asking for the backups to be picked up. I prefer it that way. As I describe it, it may seem complex to do multiple steps when one step will do. However, this solution promises that the backups are ready and […]

My solution for copying backups around the homelab Read More »

Upgrading PostgreSQL in place on FreeBSD

Leave a Comment / FreeBSD, General, Open Source, PostgreSQL / By / /

I’ve updated one of my PostgreSQL instances to PostgreSQL 18, it’s time to update the others. This time, I’m going to try pg_update. My usual approach is pg_dump and pg_restore. As this is my first attempt doing this, I’m posting this mostly for future reference when I try this again. There will be another blog post when I try this again. Which should be soon. This paragraph will link to that post when

Upgrading PostgreSQL in place on FreeBSD Read More »

PostgreSQL: MD5 password support is deprecated – updating the user passwords

Leave a Comment / General, PostgreSQL / By / /

Eight years ago, PostgreSQL introduced scram-sha-256 hashes for passwords. Eleven months ago, MD5 was deprecated. Yesterday, I got caught up with all this. Some of this post will deal with how I fixed it, but mostly it is documenting (for myself) what I did. The fix covers several services and takes place over multiple days. First, some background on why this change has come into focus for me. I have a FreeBSD jail

PostgreSQL: MD5 password support is deprecated – updating the user passwords Read More »

Reproducing a batch insert for Bacula on PostgreSQL

Leave a Comment / Bacula, Open Source, PostgreSQL / By /

This work from August 2022 was interesting at the time. My database server gets slow from time to time. It seems to happen repeatedly. I’m not sure why, but inserting large numbers of rows takes a very long time. This is a typical use-case for Bacula when recording what was backed up. I started with this, the duplication of the batch table created when Bacula is storing attribute for later insertion into the

Reproducing a batch insert for Bacula on PostgreSQL Read More »

Loading up an old copy of a PostgreSQL database, zfs snapshots, and sanoid snapshot management

Leave a Comment / FreeBSD, Open Source, PostgreSQL, sanoid, snapshots, ZFS / By /

Sometimes you want to go back to a known good state. And you want to do it quickly, without waiting around. In my case, that’s going to be a zfs snapshot. Today I am preparing to test some code changes so that FreshPorts properly creates a new category. See these recent blog posts for details: Welcome to the new category: filesystems Fixing the category creation code In this post: FreeBSD 14.1-RELEASE-p5 postgresql16-server-16.4 dbclone

Loading up an old copy of a PostgreSQL database, zfs snapshots, and sanoid snapshot management Read More »

Moving local settings for pg_hba.conf and postgresql.conf out of PGDATA

2 Comments / FreeBSD, Open Source, PostgreSQL / By / /

One of the configuration aspects of FreeBSD I have long liked is the concept of default values which are overridden by the user. For example, /etc/defaults/rc.conf (see The /etc directory). The default values in this file can be overridden by the user with their preferred values in /etc/rc.conf (or /etc/rc.conf.local, and other locations if you so choose (search for rc_conf_files)). With that approach in mind, I wanted to do the same thing with

Moving local settings for pg_hba.conf and postgresql.conf out of PGDATA Read More »

Figuring out the upgrade path for AWS RDS from PostgreSQL 12.14 to PostgreSQL 16.1

Leave a Comment / AWS, FreshPorts, Open Source, PostgreSQL / By /

These are my notes on figuring out an upgrade path for the FreshPorts PostgreSQL 12.14 database hosted on Amazon RDS. Most of this is based on Upgrading the PostgreSQL DB engine for Amazon RDS The sections of this post relate to the sections found at the above URL. First, I installed the AWS CLI (via py39-awscli, created an Access Key for my AWS login, and ran: Note that on line 4 I did

Figuring out the upgrade path for AWS RDS from PostgreSQL 12.14 to PostgreSQL 16.1 Read More »

Bacula: Moving from 9.x to 13.x and upgrading the PostgreSQL database

8 Comments / Bacula, FreeBSD, Open Source, PostgreSQL / By /

I have been using Bacula since 2004. It is my backup solution of choice, not only because it has a PostgreSQL backend, but it is flexible, robust, and reliable. I, on the other hand, have long ignored recent releases. I’m using Bacula 9.6.7 (released on 2021-01-26), roughly 3 years ago. Today, I started the upgrade to 13.0.1 (still not the latest release, but that’s because of a minor compile issue, not yet resolved;

Bacula: Moving from 9.x to 13.x and upgrading the PostgreSQL database Read More »

I figured out why pg_dump was failing with PostgreSQL 15-16

Leave a Comment / Bacula, FreeBSD, Open Source, PostgreSQL / By / /

In recent blog post, I outlined a problem I hit with pg_dump. Specifically, pg_dump was picking up and using ~/.pgpass with pg_dump from PostgreSQL 12-14, but with PostgreSQL 15-16, it was failing. In this blog post: FreeBSD 13.2 PostgreSQL server 12 / 16 PostgreSQL client 12-16 Bacula 9.6.7 Today we figured out why: $HOME. $HOME for the script was set to / In PostgreSQL < 15, the code used the database to determine

I figured out why pg_dump was failing with PostgreSQL 15-16 Read More »

While restoring my PostgreSQL database to a new FreeBSD server, I discovered the wrong database server in a configuration file

Leave a Comment / Bacula, FreeBSD, Open Source, PostgreSQL / By /

Lately, I’ve been preparing to move from PostgreSQL 12 to PostgreSQL 16 – my main developement database server at home needs to get updated. The goal: migrate each database from the old host (running PostgreSQL 12) to the new host (running PostgreSQL 16) using pg_dump and pg_restore. Today, I decided to migrate the Bacula database. I ran a pg_dump on pg02 today. Then a pg_restore on pg03. That took about 6 hours I

While restoring my PostgreSQL database to a new FreeBSD server, I discovered the wrong database server in a configuration file Read More »

Scroll to Top