Today I’ve been investigating issues related to the new “Avoid use of insecure search_path settings in pg_dump and other client programs” behaviour introduced into postgresql 9.5.12 and postgresql 10.3.
There’s a couple items that impact Discourse users:
backups taken using pg_dump of 9.5.12+ or 10.3+ are unable to be restored
this is due to the public schema now being explicitly referenced inside the restore script
Discourse currently does a restore into the restore schema then switches that to the public schema - this now breaks
pg_dump pointed at pgbouncer can cause a full or partial site outage
an unintended side effect occurs that results in a null schema search_path being applied to anyone sharing the actual connection
this will cause the site to go down as the app servers can no longer implicitly see the public schema (i.e. all the data) until the connection is recycled
As a stopgap, is there some way to push Docker images that have 9.5.11 in them so that we can go back to our dangerous, but happy lives? For me the discourse_dev image is what’s generating the backup that I really, really, wanted to show to people.
Here’s what I know. Thanks to @RGJ, I had this work-around worked out:
./launcher enter app
sv stop unicorn
sv stop redis
su postgres -c 'psql'
drop database discourse;
create database discourse;
\q
cd public/backups/default/
BACKUP=`ls |head`
zcat $BACKUP | su discourse -c 'psql discourse'
sv start unicorn
sv start redis
rake db:migrate
This used to restore my no-files backup, but now it’s failing with
In the meanwhile as a workaround in a container you can backrev your postgresql-client-9.5 packages to below 9.5.12 (or below 10.3) and the backup will work.
More or less: under the hood we restore to the restore schema and then atomically switch that to the public (production) schema so that the site is “up” during the restore. But these new versions of pg_dump generate a script that explicitly refers to the public schema… restore fails.