pg_restore creates public schema?
pg_dump 9.6.24
pg_restore 13.8
Why does pg_restore explicitly create "public" even though public is
automatically created when the database is created?
I noticed that when using "--exit-on-error". It's disappointing, because I
had to remove that option, which caused the restore to ignore other,
actually important errors.)
$ cd /var/lib/pgsql/backups/dumps/2022-10-04
$ pg_restore -vcC --if-exists --jobs=12 -Fd -d postgres CDSLBXW
pg_restore: connecting to database for restore
pg_restore: dropping DATABASE CDSLBXW
pg_restore: processing item 10813 ENCODING ENCODING
pg_restore: processing item 10814 STDSTRINGS STDSTRINGS
pg_restore: processing item 10815 SEARCHPATH SEARCHPATH
pg_restore: processing item 10816 DATABASE CDSLBXW
pg_restore: creating DATABASE "CDSLBXW"
pg_restore: connecting to new database "CDSLBXW"
pg_restore: processing item 14 SCHEMA cds
pg_restore: creating SCHEMA "cds"
pg_restore: processing item 18 SCHEMA dba
pg_restore: creating SCHEMA "dba"
pg_restore: processing item 10 SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 10; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR: schema "public" already
exists
Command was: CREATE SCHEMA public;
--
Angular momentum makes the world go 'round.
Ron <ronljohnsonjr@gmail.com> writes:
pg_dump 9.6.24
You realize that that version's been out of support for a year?
Why does pg_restore explicitly create "public" even though public is
automatically created when the database is created?
We fixed that in v11 (see 5955d9341). Evidently the fix requires an
updated pg_dump more than pg_restore. However, I believe that only
happens with -c, so why are you using both -c and -C?
regards, tom lane
On 10/6/22 09:49, Tom Lane wrote:
Ron <ronljohnsonjr@gmail.com> writes:
pg_dump 9.6.24
You realize that that version's been out of support for a year?
Yes, which is why I'm dumping from an EOL cluster, and restoring to a
supported version.
Why does pg_restore explicitly create "public" even though public is
automatically created when the database is created?We fixed that in v11 (see 5955d9341). Evidently the fix requires an
updated pg_dump more than pg_restore.
Ah.
However, I believe that only happens with -c, so why are you using both -c and -C?
Because the database might already exist on the target before doing the restore.
--
Angular momentum makes the world go 'round.
Ron <ronljohnsonjr@gmail.com> writes:
On 10/6/22 09:49, Tom Lane wrote:
Ron <ronljohnsonjr@gmail.com> writes:
pg_dump 9.6.24
You realize that that version's been out of support for a year?
Yes, which is why I'm dumping from an EOL cluster, and restoring to a
supported version.
But why are you using the dead version's pg_dump? You could use
the supported version of that.
regards, tom lane
On 10/6/22 10:20, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
On 10/6/22 09:49, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
pg_dump 9.6.24
You realize that that version's been out of support for a year?
Yes, which is why I'm dumping from an EOL cluster, and restoring to a
supported version.But why are you using the dead version's pg_dump? You could use
the supported version of that.
Because installing new software on production servers requires hurdles
(Service Now change ticket approved by the application support manager,
Delivery Service Manager, Engineering Change Board, and a one week lead time
before installing during the Saturday night maintenance window) that I'm not
willing to jump through just to take an /ad hoc/ database backup.
--
Angular momentum makes the world go 'round.
On Oct 6, 2022, at 09:46, Ron <ronljohnsonjr@gmail.com> wrote:
Because installing new software on production servers requires hurdles (Service Now change ticket approved by the application support manager, Delivery Service Manager, Engineering Change Board, and a one week lead time before installing during the Saturday night maintenance window) that I'm not willing to jump through just to take an ad hoc database backup.
Running the new pg_dump doesn't require that it be installed on the server, just that it have access to it. (I understand there may be access restrictions that make that inconvenient as well, but presumably *some* servers have access to 5432 and can be used to run pg_dump.)
On 10/6/22 11:48, Christophe Pettus wrote:
On Oct 6, 2022, at 09:46, Ron <ronljohnsonjr@gmail.com> wrote:
Because installing new software on production servers requires hurdles (Service Now change ticket approved by the application support manager, Delivery Service Manager, Engineering Change Board, and a one week lead time before installing during the Saturday night maintenance window) that I'm not willing to jump through just to take an ad hoc database backup.Running the new pg_dump doesn't require that it be installed on the server, just that it have access to it. (I understand there may be access restrictions that make that inconvenient as well, but presumably *some* servers have access to 5432 and can be used to run pg_dump.)
There is, and that is of course the first thing I thought of. Sadly, that VM
doesn't have nearly enough disk space to hold the backup folder. (It's a
tiny 2 CPU, 8GB RAM system with 75GB disk who's only purpose is to
concentrate all the scripts needed to manage 8 servers in one place and
crontab.)
--
Angular momentum makes the world go 'round.
On Oct 6, 2022, at 10:44, Ron <ronljohnsonjr@gmail.com> wrote:
Sadly, that VM doesn't have nearly enough disk space to hold the backup folder.
Use file mode, and stream the output via scp/ssh to a different machine?
On 10/6/22 10:46, Christophe Pettus wrote:
On Oct 6, 2022, at 10:44, Ron <ronljohnsonjr@gmail.com> wrote:
Sadly, that VM doesn't have nearly enough disk space to hold the backup folder.Use file mode, and stream the output via scp/ssh to a different machine?
Or Plan B:
1) Use pg_dump 9.6.24 on existing(going EOL) server
2) Set up a 9.6.24 instance somewhere you have control.
3) pg_restore to it.
4) Then use pg_dump 13.8 on the new instance.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/6/22 09:46, Ron wrote:
On 10/6/22 10:20, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
On 10/6/22 09:49, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
pg_dump 9.6.24
You realize that that version's been out of support for a year?
Yes, which is why I'm dumping from an EOL cluster, and restoring to a
supported version.But why are you using the dead version's pg_dump? You could use
the supported version of that.Because installing new software on production servers requires hurdles
(Service Now change ticket approved by the application support manager,
Delivery Service Manager, Engineering Change Board, and a one week lead
time before installing during the Saturday night maintenance window)
that I'm not willing to jump through just to take an /ad hoc/ database
backup.
1) So I assume that means Postgres 13.8 has not been installed in
anticipation of the change over?
2) All those hoops, yet you can move the data off site with no issue?
--
Angular momentum makes the world go 'round.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/6/22 12:46, Christophe Pettus wrote:
On Oct 6, 2022, at 10:44, Ron <ronljohnsonjr@gmail.com> wrote:
Sadly, that VM doesn't have nearly enough disk space to hold the backup folder.Use file mode, and stream the output via scp/ssh to a different machine?
I thought of that, too. Unfortunately, the ssh version in RHEL 8.6 is
sufficiently old that "three way" ssh (person at HostA wanting to transfer a
file from Server1 to Server2) requires that port 22 be open from Server1 to
Server2.
--
Angular momentum makes the world go 'round.
On 10/6/22 14:32, Adrian Klaver wrote:
On 10/6/22 10:46, Christophe Pettus wrote:
On Oct 6, 2022, at 10:44, Ron <ronljohnsonjr@gmail.com> wrote:
Sadly, that VM doesn't have nearly enough disk space to hold the backup
folder.Use file mode, and stream the output via scp/ssh to a different machine?
Or Plan B:
1) Use pg_dump 9.6.24 on existing(going EOL) server
2) Set up a 9.6.24 instance somewhere you have control.
3) pg_restore to it.
4) Then use pg_dump 13.8 on the new instance.
While that would certainly work, it's a heck of a lot of extra effort for
large one-time operations.
--
Angular momentum makes the world go 'round.
On 10/6/22 14:35, Adrian Klaver wrote:
On 10/6/22 09:46, Ron wrote:
On 10/6/22 10:20, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
On 10/6/22 09:49, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
pg_dump 9.6.24
You realize that that version's been out of support for a year?
Yes, which is why I'm dumping from an EOL cluster, and restoring to a
supported version.But why are you using the dead version's pg_dump? You could use
the supported version of that.Because installing new software on production servers requires hurdles
(Service Now change ticket approved by the application support manager,
Delivery Service Manager, Engineering Change Board, and a one week lead
time before installing during the Saturday night maintenance window) that
I'm not willing to jump through just to take an /ad hoc/ database backup.1) So I assume that means Postgres 13.8 has not been installed in
anticipation of the change over?
It's certainly been installed on the *new* (RHEL8) server. Not the EOL RHEL6
server, because of course the point is to get off of EOL software...
2) All those hoops,
Those hoops are for installing new software on a server. We jumped through
those hoops six months ago to upgrade Pg 9.6.18 to .24 on the RHEL6 server
yet you can move the data off site with no issue?
This post was about pg_restore creating "public", not about how to copy
files from point A to point B.
--
Angular momentum makes the world go 'round.
On 10/6/22 2:03 PM, Ron wrote:
On 10/6/22 14:35, Adrian Klaver wrote:
On 10/6/22 09:46, Ron wrote:
On 10/6/22 10:20, Tom Lane wrote:
Because installing new software on production servers requires
hurdles (Service Now change ticket approved by the application
support manager, Delivery Service Manager, Engineering Change Board,
and a one week lead time before installing during the Saturday night
maintenance window) that I'm not willing to jump through just to take
an /ad hoc/ database backup.1) So I assume that means Postgres 13.8 has not been installed in
anticipation of the change over?It's certainly been installed on the *new* (RHEL8) server. Not the EOL
RHEL6 server, because of course the point is to get off of EOL software...
And the RHEL8 server can't talk to the RHEL6 server?
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/6/22 1:54 PM, Ron wrote:
On 10/6/22 14:32, Adrian Klaver wrote:
On 10/6/22 10:46, Christophe Pettus wrote:
On Oct 6, 2022, at 10:44, Ron <ronljohnsonjr@gmail.com> wrote:
Sadly, that VM doesn't have nearly enough disk space to hold the
backup folder.Use file mode, and stream the output via scp/ssh to a different machine?
Or Plan B:
1) Use pg_dump 9.6.24 on existing(going EOL) server
2) Set up a 9.6.24 instance somewhere you have control.
3) pg_restore to it.
4) Then use pg_dump 13.8 on the new instance.
While that would certainly work, it's a heck of a lot of extra effort
for large one-time operations.
1) It could be scripted.
2) Nothing to stop you from splitting into schema only dump for the 9.6
--> 9.6 --> 13 restore of schema.
3) Then data only dump restored directly to 13.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/6/22 17:01, Adrian Klaver wrote:
On 10/6/22 2:03 PM, Ron wrote:
On 10/6/22 14:35, Adrian Klaver wrote:
On 10/6/22 09:46, Ron wrote:
On 10/6/22 10:20, Tom Lane wrote:
Because installing new software on production servers requires hurdles
(Service Now change ticket approved by the application support manager,
Delivery Service Manager, Engineering Change Board, and a one week lead
time before installing during the Saturday night maintenance window)
that I'm not willing to jump through just to take an /ad hoc/ database
backup.1) So I assume that means Postgres 13.8 has not been installed in
anticipation of the change over?It's certainly been installed on the *new* (RHEL8) server. Not the EOL
RHEL6 server, because of course the point is to get off of EOL software...And the RHEL8 server can't talk to the RHEL6 server?
Give me /some/ credit for thinking of that first...
--
Angular momentum makes the world go 'round.
On 6 Oct 2022, at 22:52, Ron <ronljohnsonjr@gmail.com> wrote:
On 10/6/22 12:46, Christophe Pettus wrote:
On Oct 6, 2022, at 10:44, Ron <ronljohnsonjr@gmail.com> wrote:
Sadly, that VM doesn't have nearly enough disk space to hold the backup folder.Use file mode, and stream the output via scp/ssh to a different machine?
I thought of that, too. Unfortunately, the ssh version in RHEL 8.6 is sufficiently old that "three way" ssh (person at HostA wanting to transfer a file from Server1 to Server2) requires that port 22 be open from Server1 to Server2.
Bit of a wild idea, I haven’t used SSH tunnels in years, so a bit fuzzy on the details:
Can you create an SSH tunnel to the new machine from the VM, then pipe that to an SSH connection from a machine that does have enough space to dump?
And then vice versa to the new machine to restore? (Unless access to that one is easier of course)
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
On 10/7/22 10:11, Ron wrote:
And the RHEL8 server can't talk to the RHEL6 server?
Give me /some/ credit for thinking of that first...
Also give me credit for not assuming what you have done and instead
confirming it. I have been in involved in or followed multiple threads
on this list where the participants assumed the obvious and not until
some point deep in the thread did anyway get around to confirming the
assumption and in doing so solved the problem. So I will continue to ask
the obvious when it is not explicitly stated.
--
Angular momentum makes the world go 'round.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/7/22 15:31, Alban Hertroys wrote:
On 6 Oct 2022, at 22:52, Ron <ronljohnsonjr@gmail.com> wrote:
On 10/6/22 12:46, Christophe Pettus wrote:
On Oct 6, 2022, at 10:44, Ron <ronljohnsonjr@gmail.com> wrote:
Sadly, that VM doesn't have nearly enough disk space to hold the backup folder.Use file mode, and stream the output via scp/ssh to a different machine?
I thought of that, too. Unfortunately, the ssh version in RHEL 8.6 is sufficiently old that "three way" ssh (person at HostA wanting to transfer a file from Server1 to Server2) requires that port 22 be open from Server1 to Server2.
Bit of a wild idea, I haven’t used SSH tunnels in years, so a bit fuzzy on the details:
Can you create an SSH tunnel to the new machine from the VM, then pipe that to an SSH connection from a machine that does have enough space to dump?
Interesting. (It's above my ssh expertise, though.)
--
Angular momentum makes the world go 'round.
On 10/7/22 20:14, Ron wrote:
On 10/7/22 15:31, Alban Hertroys wrote:
Can you create an SSH tunnel to the new machine from the VM, then pipe
that to an SSH connection from a machine that does have enough space
to dump?Interesting. (It's above my ssh expertise, though.)
An example using psql:
https://fedingo.com/how-to-connect-to-postgresql-server-via-ssh-tunnel/
--
Adrian Klaver
adrian.klaver@aklaver.com