# PostgreSQL 9.5.12 和 10.3 引入的变更导致备份相关故障
**URL:** https://meta.discourse.org/t/changes-introduced-in-postgresql-9-5-12-and-10-3-cause-backup-related-failures/82555
**Category:** Bug
**Created:** [2018 年3 月 8 日 21:50 UTC](https://meta.discourse.org/t/changes-introduced-in-postgresql-9-5-12-and-10-3-cause-backup-related-failures/82555 "2018-03-08T21:50:57Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@supermathie](https://meta.discourse.org/u/supermathie)
#### Post date: [2018 年3 月 8 日 21:50 UTC](https://meta.discourse.org/t/changes-introduced-in-postgresql-9-5-12-and-10-3-cause-backup-related-failures/82555/1 "2018-03-08T21:50:57Z")
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](https://www.postgresql.org/docs/9.5/static/release-9-5-12.html) and [postgresql 10.3](https://www.postgresql.org/docs/10/static/release-10-3.html).
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**
- **`pg_dump` pointed at `pgbouncer` can cause a full or partial site outage**
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [2018 年3 月 8 日 23:11 UTC](https://meta.discourse.org/t/changes-introduced-in-postgresql-9-5-12-and-10-3-cause-backup-related-failures/82555/2 "2018-03-08T23:11:23Z")
Thanks, @supermathie. Glad you’re on this.
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:
```plaintext
./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
```plaintext
--------
1
(1 row)
ERROR: duplicate key value violates unique constraint "ar_internal_metadata_pkey"
DETAIL: Key (key)=(environment) already exists.
CONTEXT: COPY ar_internal_metadata, line 1
ERROR: duplicate key value violates unique constraint "badge_groupings_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY badge_groupings, line 1
setval
--------
```
And a bunch of other errors that are basically duplicate key kinds of things. It’s as if the database didn’t actually get dropped or. . . something?
---
### Author:  [@supermathie](https://meta.discourse.org/u/supermathie)
#### Post date: [2018 年3 月 8 日 23:34 UTC](https://meta.discourse.org/t/changes-introduced-in-postgresql-9-5-12-and-10-3-cause-backup-related-failures/82555/3 "2018-03-08T23:34:55Z")
> [@pfaffman](#):
>
> 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?
We’re looking at this, yes.
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.
> [@pfaffman](#):
>
> It’s as if the database didn’t actually get dropped or. . . something?
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.
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [2018 年3 月 9 日 05:45 UTC](https://meta.discourse.org/t/changes-introduced-in-postgresql-9-5-12-and-10-3-cause-backup-related-failures/82555/4 "2018-03-09T05:45:52Z")
> [@supermathie](#):
>
> pg\_dump pointed at pgbouncer can cause a full or partial site outage
This is fixed in
[https://github.com/discourse/discourse/commit/a89f3160a53081adbd2c05c4b3b146414500248a](https://github.com/discourse/discourse/commit/a89f3160a53081adbd2c05c4b3b146414500248a)
---
### Author:  [@supermathie](https://meta.discourse.org/u/supermathie)
#### Post date: [2018 年3 月 10 日 21:00 UTC](https://meta.discourse.org/t/changes-introduced-in-postgresql-9-5-12-and-10-3-cause-backup-related-failures/82555/5 "2018-03-10T21:00:25Z")
We also need to handle a multisite configuration - a multisite won’t see the new `backup_port` unless it’s explicitly in the config:
base site:
```plaintext
[2] pry(main)> ActiveRecord::Base.connection_pool.spec.config
=> {:adapter=>"postgresql_fallback",
:pool=>25,
:connect_timeout=>5,
:timeout=>5000,
:host=>"2001:db8::1",
:port=>6432,
:backup_port=>5432,
:username=>"pets",
:password=>"pets",
:replica_host=>"2001:db8::1001",
:replica_port=>6432,
:host_names=>["pets.discourse.org"],
:database=>"pets",
:prepared_statements=>false}
```
multisite:
```plaintext
[9] pry(main)> ActiveRecord::Base.connection_pool.spec.config
=> {:host=>"2001:db8::2",
:replica_host=>"2001:db8::1002",
:database=>"kittens",
:username=>"kittens",
:password=>"fluffy",
:host_names=>["community.fluffykittens.com"],
:pool=>8,
:timeout=>5000,
:adapter=>"postgresql_fallback",
:prepared_statements=>false,
:port=>6432,
:replica_port=>6432,
:connect_timeout=>5,
:db_key=>"kittens"}
```
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [2018 年5 月 1 日 01:48 UTC](https://meta.discourse.org/t/changes-introduced-in-postgresql-9-5-12-and-10-3-cause-backup-related-failures/82555/7 "2018-05-01T01:48:26Z")
Now that we updated the base image, is there anything left to do here?
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [2018 年5 月 7 日 07:22 UTC](https://meta.discourse.org/t/changes-introduced-in-postgresql-9-5-12-and-10-3-cause-backup-related-failures/82555/8 "2018-05-07T07:22:33Z")