Users and Roles in PostgreSQL are very similar. When I set up the FreshPorts database back in mid-2000, I was using PostgreSQL 7.0.3 (that’s my best guess based on my blog entry). I suspect roles were not available then and were introduced with PostgreSQL 8. I am positive someone will correct me if that’s wrong. …
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 …
Creating read-only PostgreSQL database users for pg_dump and pg_dumpall Read More »
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 …
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 …
Dumping PostgreSQL 9.4 to upgrade to PostgreSQL 9.6 Read More »
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 …
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 …
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 …
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 …
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 …
pg_dump: aborting because of server version mismatch Read More »
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.