Horton

Re: Non-text mode for pg_dumpall

Andrew Dunstan <andrew@dunslane.net>

View thread
From: Andrew Dunstan <andrew@dunslane.net>
To: Mahendra Singh Thalor <mahi6run@gmail.com>
Cc: jian he <jian.universality@gmail.com>, Álvaro Herrera <alvherre@alvh.no-ip.org>, Srinath Reddy <srinath2133@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2025-03-31T13:57:56Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add non-text output formats to pg_dumpall

  2. Improve pg_dump/pg_dumpall help synopses and terminology

  3. Non text modes for pg_dumpall, correspondingly change pg_restore

  4. Doc: manually break lines in wide UUID examples.

On 2025-03-31 Mo 5:34 AM, Mahendra Singh Thalor wrote:
>
> >
> > There are a couple of rough edges, though.
> >
> > First, I see this:
> >
> >
> > andrew@ub22arm:inst $ bin/pg_restore -C -d postgres
> > --exclude-database=regression_dummy_seclabel
> > --exclude-database=regression_test_extensions
> > --exclude-database=regression_test_pg_dump dest
> > pg_restore: error: could not execute query: "ERROR:  role "andrew"
> > already exists
> > "
> > Command was: "
> >
> > --
> > -- Roles
> > --
> >
> > CREATE ROLE andrew;"
> > pg_restore: warning: errors ignored on global.dat file restore: 1
> > pg_restore: error: could not execute query: ERROR:  database "template1"
> > already exists
> > Command was: CREATE DATABASE template1 WITH TEMPLATE = template0
> > ENCODING = 'SQL_ASCII' LOCALE_PROVIDER = libc LOCALE = 'C';
> >
> >
> > pg_restore: warning: errors ignored on database "template1" restore: 1
> > pg_restore: error: could not execute query: ERROR:  database "postgres"
> > already exists
> > Command was: CREATE DATABASE postgres WITH TEMPLATE = template0 ENCODING
> > = 'SQL_ASCII' LOCALE_PROVIDER = libc LOCALE = 'C';
> >
> >
> > pg_restore: warning: errors ignored on database "postgres" restore: 1
> > pg_restore: warning: errors ignored on restore: 3
> >
> >
> >
> > It seems pointless to be trying to create the rolw that we are connected
> > as, and we also expect template1 and postgres to exist.
>
> Thanks Andrew for the updated patches.
>
> Here, I am attaching a delta patch which solves the errors for the 
> already created database and we need to reset some flags also. Please 
> have a look over this delta patch and merge it.
>
> If we want to skip errors for connected user (CREATE ROLE username), 
> then we need to handle it by comparing sql commands in 
> process_global_sql_commands function or we can compare errors after 
> executing it.
> delta_0002* patch is doing some handling but this is not a proper fix.
>
> I think we can merge delta_0001* and later, we can work on delta_0002.


Yes, delta 1 looks OK, except that the pstrdup() calls are probably 
unnecessary. Delta 2 needs some significant surgery at least. I think we 
can use it as at least a partial fix, to avoid trying to create the role 
we're running as (Should use PQuser() for that rather than cparams.user).

BTW, if you're sending delta patches, make sure they don't have .patch 
extensions. Otherwise, the CFBot gets upset. I usually just add .noci to 
the file names.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com