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
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
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
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.
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)|
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)|
- SAWADA Masahiko: Understanding the VACUUM source code
- HAYAMIZU Yuto: Performance Measurements Pt. II - Practical Session
- NAKANISHI Yoshinori: Monitoring PostgreSQL with Zabbix
(subject titles are my own translation)
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.
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.