PostgreSQL

PostgreSQL – convert a user to a role

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. …

PostgreSQL – convert a user to a role Read More »

Creating read-only PostgreSQL database users for pg_dump and pg_dumpall

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 »

Dumping PostgreSQL 9.4 to upgrade to PostgreSQL 9.6

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 »

PostgreSQL – logging to a file

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 …

PostgreSQL – logging to a file Read More »

Pentabarf email tokens

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 …

Pentabarf email tokens Read More »

pg_dump: aborting because of server version mismatch

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 »

Bacula: indexing on media.lastwritten

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.

Scroll to Top