PostgreSQL

Creating read-only PostgreSQL database users for pg_dump and pg_dumpall

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

See also mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces. I’m linking this because I use both articles on a regular basis. Sometimes you want a user which can only dump the database, but nothing else. Fortunately, I searched, and found a solution. I’m writing it down so I only have to search this blog. I want the user rsyncer […]

Creating read-only PostgreSQL database users for pg_dump and pg_dumpall Read More »

Moving FreshPorts

Leave a Comment / DNS, FreeBSD, FreshPorts, PostgreSQL / By /

Today I moved the FreshPorts website from one server to another. My goal is for nobody to notice. In preparation for this move, I have: DNS TTL reduced to 60s Posted to Twitter Updated the status page Put the website put in offline mode: Sorry, the website is offline from 2017-11-05 17:01, for approximately 3 hours. It is now 2017-11-05 17:01. That works for both http and https: [dan@air01:~] $ wget -S http://www.freshports.org

Moving FreshPorts Read More »

Dumping PostgreSQL 9.4 to upgrade to PostgreSQL 9.6

Leave a Comment / ezjail, FreshPorts, Jails, PostgreSQL / By /

FreeBSD 10.3 & PostgreSQL 9.4 on the old server, and FreeBSD 11.1 & PostgreSQL 9.6 on the new server. As part of the move from the old FreshPorts server (photos) to the new FreshPorts server, I’ll need to dump the database and copy it over. What? No zfs send? There is no ZFS on the old server. The rule of thumb when upgrading a PostgreSQL database is to dump with the pg_dump client

Dumping PostgreSQL 9.4 to upgrade to PostgreSQL 9.6 Read More »

PostgreSQL – logging to a file

Leave a Comment / PostgreSQL, syslog / By / /

These steps were carried out on FreeBSD 11.0 with PostgreSQL 9.6 (two of my favorite tools). I like logging. I like logging PostgreSQL. With logs, you can see what happened. Without, you can only guess. Setting up logging for PostgreSQL involves several parts, each of which must be completed or else I don’t get what I want. This is not a criticism of PostgreSQL. It’s a feature. I am documenting this because each

PostgreSQL – logging to a file Read More »

Pentabarf email tokens

Leave a Comment / Pentabarf / By /

As found at: http://web.archive.org/web/20160309091535/http://pentabarf.org/Email Variables The following variables may be used in the text and subject of the mail {{name}} The name of the recipient. {{person_id}} The person-id of the recipient. {{conference_acronym}} The acronym of the conference if the recipients are conference specific. {{conference_title}} The title of the current conference if the recipients are conference specific. {{email}} The email address of the recipient {{event_title}} A comma-separated list of the events in question.

Pentabarf email tokens Read More »

PGCon 2016 charity auction

Leave a Comment / PostgreSQL / By /

Every year PGCon holds a charity auction as part of the closing session. All proceeds go to The Ottawa Mission, a local group. The auction includes items you would keep as art, and some you would consume before you left town. Others, such as empty paper bags or cardboard boxes are left in the recycling bin. It is all in good fun. 1 Chocolate from Japan $20.00 2 Elephant with cloud $220.00 3

PGCon 2016 charity auction Read More »

Bacula on FreeBSD with ZFS

Leave a Comment / Advocacy, Backups, Bacula, FreeBSD, PostgreSQL, ZFS / By /

Full disclosure: I’m biased. I like the combination of PostgreSQL, FreeBSD, ZFS, and Bacula. In fact, I like it so much, I wrote the PostgreSQL backend for Bacula. Since then, I have been using Bacula for over 10 years. I have backed up to HDD, DDS, DLT, and SDLT. Bacula has proven to be reliable and it just works. Clients are available for a wide range of operating systems and Bacula doesn’t care

Bacula on FreeBSD with ZFS Read More »

pg_dump: aborting because of server version mismatch

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

Here’s something to be aware of. Update your PostgreSQL client on your Bacula client if it is used for backing up the Catalog. Ngaios just told me: FILE_AGE WARNING: /usr/backups/bacula/MyCatalog.dump is 45125 seconds old and 0 bytes I looked at the backup job from earlier today: 28-Dec 08:15 bacula-dir JobId 195194: shell command: run BeforeJob “/usr/local/bacula/make_catalog_backup.new MyCatalog” 28-Dec 08:15 bacula-dir JobId 195194: BeforeJob: pg_dump: server version: 9.4.0; pg_dump version: 9.3.5 28-Dec 08:15 bacula-dir

pg_dump: aborting because of server version mismatch Read More »

Bacula: indexing on media.lastwritten

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

I wrote this nearly a year ago, but did not publish it then. Tonight I found this query running, so I tried an explain analyse on it: That’s 10 seconds. Add an index: bacula=# create index media_lastwritten_idx on media(lastwritten); CREATE INDEX Run the query again: Forgive my math, but that’s over 10,000 times faster now.

Bacula: indexing on media.lastwritten Read More »

Using ezjail-admin archive to create a new jail, almost like an existing jail

Leave a Comment / ezjail, FreeBSD, Jails, PostgreSQL / By /

I use FreeBSD Jails. I use them a lot. I have jails for websites. I have jails for regression testing, mail servers, OpenVPN servers, etc. I like jails for many reasons. One of which is being able to create a new jail which is pretty much identical to another jail, except for a few things. In this case, I wanted to create a new jail to do regression testing for Bacula, the best

Using ezjail-admin archive to create a new jail, almost like an existing jail Read More »