sql-info.de
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | Index

There have been a lot of articles about new features in the recent PostgreSQL 13 release, but one set of improvements which seems to have escaped much attention is in pg_rewind, which has gained some additional useful functionality deserving of closer examination.

New option "-R /--write-recovery-conf"

As the name implies, this option causes pg_rewind to write recovery (replication) configuration (and additionally the "standby.signal" file) after rewinding a server, in exactly the same way that pg_basebackup does. This is useful for example when restoring a former primary to become a standby. Note that it can only be used in conjunction with the option --source-server, and (unless the --dry-run option is supplied) that the configuration will be written regardless of whether a rewind is needed.

As replication configuration via the recovery.conf file was removed in PostgreSQL 12, the generated replication configuration is actually appended to postgresql.auto.conf, which is the one configuration file which is guaranteed to be in a known location (the data directory) and which will be read last when PostgreSQL starts up, ensuring any prior replication configuration is overridden by whatever pg_rewind has written.

Currently the only replication configuration item actually written is primary_conninfo - be aware that this is generated from a blend of the connection information passed in --source-server and the default parameters generated by libpq, which may be different to the primary_conninfo string you would normally generate and will look something like this:

primary_conninfo = 'user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=prefer host=node1 port=5432
sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

Note that currently replication slot configuration (primary_slot_name) cannot be generated by pg_rewind.

Also be aware that if attempting to rewind a standby which was not shut down properly, the standby.signal file must be removed otherwise pg_rewind will attempt to start the server in single user mode to run recovery, which cannot be done with replication configuration in place. See also the section "Automatic crash recovery" below.

This option was added in commit 927474ce

Posted at 10:31 AM

Japan has been an early and vigorous adopter of PostgreSQL (back in 2006, when PostgreSQL was still...

Posted at 10:48 PM

Let's assume you have a CSV file which looks like this:

    $ head -5 /tmp/prod.csv
    1,14,ACADEMY ACADEMY,PENELOPE GUINESS,25.99,0,1976
    2,6,ACADEMY ACE,EWAN RICKMAN,20.99,0,6289
    3,6,ACADEMY ADAPTATION,VIVIEN KAHN,28.99,0,7173
    4,3,ACADEMY AFFAIR,ALAN MARX,14.99,0,8042
    5,3,ACADEMY AFRICAN,CARRIE HANNAH,11.99,1,2183
Posted at 3:34 PM

Recently I needed to put together some simple design diagrams in a hurry and it occurred to me that pgAdmin's Graphical Query Builder would be just the thing. Admittedly its layout functionality is limited to creating boxes with tablename and columns, and drawing lines without fancy arrows between columns on different tables, but it's enough to sketch out basic entity relationships.

Posted at 11:44 AM

A recent thread on -general sparked my interest as I'm working with a large and heterogeneous code base recently converted from Oracle which makes heavy use of TO_DATE(), and it seems this function has a couple of gotchas worthy of consideration.

Posted at 5:28 AM

January 18th (Saturday):

3rd PostgreSQL Unconference@Tokyo

Time: 13:30 ~ 17:30
Location: Asushisuto Seminar Room, 4-2-1 Kudanshita-kita, Chiyoda-ku (東京都千代田区九段北4-2-1) (map)
Closest station: Ichigaya
Link: http://atnd.org/events/46187

Informal "unconference" event with no fixed agenda. Presentations mainly in Japanese, but English-speakers (and speakers in English) more than welcome. Unfortunately I won't be able to attend this time :(

February 1st (Saturday):

28th PostgreSQL Study Group

Time: 13:00 ~ 17:30
Location: AIIT Satellite Campus, 1-18-13 Soto-kanda, Chiyoda-ku (map)
Closest station: Akihabara
Link: http://www.postgresql.jp/wg/shikumi/shikumi28/view

Speakers:

  1. SAWADA Masahiko: Understanding the VACUUM source code
  2. HAYAMIZU Yuto: Performance Measurements Pt. II - Practical Session
  3. NAKANISHI Yoshinori: Monitoring PostgreSQL with Zabbix

(subject titles are my own translation)

Posted at 1:35 AM

Well I've finally fulfilled one of my 2013 New Year's resolutions, which was to write some kind of foreign data wrapper, so with no further ado here is the initial implementation. It is basically working but is still very much in the proof-of-concept/unstable/may-eat-your-data stage. Having said that, if anyone has a practical use for this I'd be very interested to hear about it - my main motivation for creating this FDW is to learn more about PostgreSQL internals.

Posted at 8:28 AM

PostgreSQL 9.3 RC1 was released the other day, and despite the dire warnings I couldn't resist putting it on this server to try out some of the new functionality in live operation. Admittedly it's not a real "production server" as having the database crash or mangle the data beyond repair would be merely an annoyance to myself and is nothing that can't be recovered from backups, so it's a good way of testing the new release. I've had good experiences with release candidates in the past, and probably the worst that could happen is the discovery of some issue requiring a bump of the catalog version number before final release, which means I'd have to upgrade from backups (which would probably mean whole minutes of downtime).

Disclaimer, esp. for any of my colleagues reading this wondering if I'm insane: there's no way I would ever do this with a genuine production installation.

Anyway, a full day's worth of log files shows no errors or other issues associated with the new release. This is particularly gratifying as there's now a (modest) custom background worker running which is giving me a warm tingly feeling and has got me thinking about ideas for new ones. I also feel a materialized view coming on, and I'm sure there's some way I could contrive a justification for using a writeable foreign data wrapper.

Thanks to everyone who has put so much effort into this release - I'm looking forward to the day when it can run in production for real.

Posted at 2:40 PM
1 comment  | 

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | Index