Horton
Menu

Thread

  1. pg_restore creates public schema?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-10-06T14:24:39Z

    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.

  2. Re: pg_restore creates public schema?

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-10-06T14:49:31Z

    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

  3. Re: pg_restore creates public schema?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-10-06T15:17:44Z

    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.

  4. Re: pg_restore creates public schema?

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-10-06T15:20:37Z

    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

  5. Re: pg_restore creates public schema?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-10-06T16:46:26Z

    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.

  6. Re: pg_restore creates public schema?

    Christophe Pettus <xof@thebuild.com> — 2022-10-06T16:48:11Z

    
> 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.)

  7. Re: pg_restore creates public schema?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-10-06T17:44:19Z

    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.

  8. Re: pg_restore creates public schema?

    Christophe Pettus <xof@thebuild.com> — 2022-10-06T17:46:25Z

    
> 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?

  9. Re: pg_restore creates public schema?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2022-10-06T19:32:27Z

    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

  10. Re: pg_restore creates public schema?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2022-10-06T19:35:21Z

    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

  11. Re: pg_restore creates public schema?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-10-06T20:52:17Z

    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.

  12. Re: pg_restore creates public schema?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-10-06T20:54:04Z

    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.

  13. Re: pg_restore creates public schema?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-10-06T21:03:32Z

    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.

  14. Re: pg_restore creates public schema?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2022-10-06T22:01:18Z

    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

  15. Re: pg_restore creates public schema?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2022-10-06T22:04:47Z

    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

  16. Re: pg_restore creates public schema?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-10-07T17:11:43Z

    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.

  17. Re: pg_restore creates public schema?

    Alban Hertroys <haramrae@gmail.com> — 2022-10-07T20:31:25Z

    > 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.

  18. Re: pg_restore creates public schema?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2022-10-07T21:36:56Z

    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

  19. Re: pg_restore creates public schema?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-10-08T03:14:10Z

    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.

  20. Re: pg_restore creates public schema?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2022-10-08T15:02:28Z

    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