Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres question

Postgres question

From
Ron Watkins
Date:
Newbie to postgres here. We have Postgres 11 server at work and we need to copy a database contents from a TEST server to a PROD server. I was looking at pg_dump, but I heard it doesn't work with binary (blob) data? Can someone clarify how to copy a database contents from one server to another server?
Ron

Re: Postgres question

From
Holger Jakobs
Date:
Hi,

Of course pg_dump can also dump blobs, and it's even the default. You
may use the option --no-blobs to leave them out.

A glance at the man page can be more informative than just hearing
something from someone.

Regards,

Holger

Am 06.11.20 um 23:02 schrieb Ron Watkins:
> Newbie to postgres here. We have Postgres 11 server at work and we
> need to copy a database contents from a TEST server to a PROD server.
> I was looking at pg_dump, but I heard it doesn't work with binary
> (blob) data? Can someone clarify how to copy a database contents from
> one server to another server?
> Ron

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachment

Re: Postgres question

From
"David G. Johnston"
Date:
On Fri, Nov 6, 2020 at 3:02 PM Ron Watkins <rwatki@gmail.com> wrote:
I was looking at pg_dump, but I heard it doesn't work with binary (blob) data?

Better to read the documentation instead of relying on general heresay.

pg_dump --blobs # "Include large objects in the dump. This is the default behavior except..."

Though I would question whether your concept of "binary (blob) data" and "large objects" are the same.

David J.

Re: Postgres question

From
Tom Lane
Date:
Holger Jakobs <holger@jakobs.com> writes:
> Of course pg_dump can also dump blobs, and it's even the default. You 
> may use the option --no-blobs to leave them out.
> A glance at the man page can be more informative than just hearing 
> something from someone.

Especially information as out of date as that :-(.  Glancing at the
commit log, the very first version of pg_dump (in PG 7.0) did not do
blobs, but that was fixed by 7.1.  I have a vague recollection that
--no-blobs might've been the default for a while after that, but
certainly it's been well over a decade since it wasn't default.

            regards, tom lane

Re: Postgres question

From
Laurenz Albe
Date:
On Fri, 2020-11-06 at 17:13 -0500, Tom Lane wrote:
> Holger Jakobs <holger@jakobs.com> writes:
> 
> > Of course pg_dump can also dump blobs, and it's even the default. You 
> > may use the option --no-blobs to leave them out.
> > A glance at the man page can be more informative than just hearing 
> > something from someone.
> 
> Especially information as out of date as that :-(.  Glancing at the
> commit log, the very first version of pg_dump (in PG 7.0) did not do
> blobs, but that was fixed by 7.1.  I have a vague recollection that
> --no-blobs might've been the default for a while after that, but
> certainly it's been well over a decade since it wasn't default.

The exception is when except when --schema, --table, or --schema-only
is specified.  Then you have to use --blobs to get the large objects
in the dump.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com