pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_dump - txt sql vs binary
10+ messages / 4 participants
[nested] [flat]

* pg_dump - txt sql vs binary
@ 2006-01-22 20:41 Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Ciprian Hodorogea @ 2006-01-22 20:41 UTC (permalink / raw)
  To: pgsql-admin

Hi All,

 

  1. I noticed that binary dumps created with Postgres 8.0.3 can not be
imported with Postgres 8.1.x, so this is not a good backup idea.

 

  2. All dumps generated with pg_dump seem to contain internal functions
also, which then raise problems when trying to import. Can these be somehow
excluded from the generated SQL backup? 

 

  All I want is to obtain a valid SQL file that I can then import within any
upcoming version of Postgres.  Does anyone have any suggestion please?

 

Best regards,

 

Ciprian Hodorogea

IntelliSoft SRL

http://www.bitsp.com/ 

 

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: pg_dump - txt sql vs binary
  2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
@ 2006-01-22 23:51 ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-23 14:35   ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2006-01-22 23:51 UTC (permalink / raw)
  To: ciprian.hodorogea@bitsp.com; +Cc: pgsql-admin

"Ciprian Hodorogea" <ciprian.hodorogea@bitsp.com> writes:
>   1. I noticed that binary dumps created with Postgres 8.0.3 can not be
> imported with Postgres 8.1.x, so this is not a good backup idea.

If you mean a filesystem dump, no.

>   2. All dumps generated with pg_dump seem to contain internal functions
> also, which then raise problems when trying to import. Can these be somehow
> excluded from the generated SQL backup? 

Details please?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: pg_dump - txt sql vs binary
  2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-01-23 14:35   ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-23 15:56     ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-23 15:57     ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
  0 siblings, 2 replies; 10+ messages in thread

From: Ciprian Hodorogea @ 2006-01-23 14:35 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

>>  2. All dumps generated with pg_dump seem to contain internal functions
>>also, which then raise problems when trying to import. Can these be somehow
>>excluded from the generated SQL backup? 

Is there a way to differentiate postgres internal functions from 
user-defined functions? (other but making up a list of those functions 
names)

Regards, Ciprian



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: pg_dump - txt sql vs binary
  2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-23 14:35   ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
@ 2006-01-23 15:56     ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 10+ messages in thread

From: Tom Lane @ 2006-01-23 15:56 UTC (permalink / raw)
  To: ciprian.hodorogea@bitsp.com; +Cc: pgsql-admin

Ciprian Hodorogea <ciprian.hodorogea@bitsp.com> writes:
> 2. All dumps generated with pg_dump seem to contain internal functions
> also, which then raise problems when trying to import. Can these be somehow
> excluded from the generated SQL backup? 

> Is there a way to differentiate postgres internal functions from 
> user-defined functions? (other but making up a list of those functions 
> names)

I can think of several possibilities, but it's not clear what you need
to accomplish.  Would you show us details of the problem you're having,
rather than jumping to conclusions about how to fix it?  Bear in mind
that the rest of the world has found no need to do any such thing while
using pg_dump.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: pg_dump - txt sql vs binary
  2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-23 14:35   ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
@ 2006-01-23 15:57     ` Michael Fuhr <mike@fuhr.org>
  2006-01-23 16:49       ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  1 sibling, 1 reply; 10+ messages in thread

From: Michael Fuhr @ 2006-01-23 15:57 UTC (permalink / raw)
  To: Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin

On Mon, Jan 23, 2006 at 04:35:20PM +0200, Ciprian Hodorogea wrote:
> >> 2. All dumps generated with pg_dump seem to contain internal functions
> >>also, which then raise problems when trying to import. Can these be 
> >>somehow excluded from the generated SQL backup? 
> 
> Is there a way to differentiate postgres internal functions from 
> user-defined functions? (other but making up a list of those functions 
> names)

What "internal" functions are you talking about?  What's the exact
command you're running and what's the exact error message or
unexpected behavior you're seeing?

-- 
Michael Fuhr



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: pg_dump - txt sql vs binary
  2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-23 14:35   ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-23 15:57     ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
@ 2006-01-23 16:49       ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-23 17:23         ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Ciprian Hodorogea @ 2006-01-23 16:49 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin

Command I have used:
pg_dump.exe -i -h localhost -p 5432 -U postgres -F p -v -f "C:\test.sql" 
bicms_server

This will put in the output SQL file postgres functions also (not only 
those defined by me), for example:

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
     AS '$libdir/plpgsql', 'plpgsql_call_handler'
     LANGUAGE c;

CREATE TYPE tablefunc_crosstab_2 AS (
	row_name text,
	category_1 text,
	category_2 text
);

CREATE FUNCTION connectby(text, text, text, text, integer, text) RETURNS 
SETOF record
     AS '$libdir/tablefunc', 'connectby_text'
     LANGUAGE c STABLE STRICT;


ALTER FUNCTION public.connectby(text, text, text, text, integer, text) 
OWNER TO postgres;


...and many others.



Anyway, my question would be: if I have postgres 8.1.x installed on 
computer A and the same version of postgres installed on computer B, 
which would be your recommended way of backing up data on computer A and 
put it on computer B?


Regards, Ciprian

Michael Fuhr wrote:
> On Mon, Jan 23, 2006 at 04:35:20PM +0200, Ciprian Hodorogea wrote:
> 
>>>>2. All dumps generated with pg_dump seem to contain internal functions
>>>>also, which then raise problems when trying to import. Can these be 
>>>>somehow excluded from the generated SQL backup? 
>>
>>Is there a way to differentiate postgres internal functions from 
>>user-defined functions? (other but making up a list of those functions 
>>names)
> 
> 
> What "internal" functions are you talking about?  What's the exact
> command you're running and what's the exact error message or
> unexpected behavior you're seeing?
> 



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: pg_dump - txt sql vs binary
  2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-23 14:35   ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-23 15:57     ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
  2006-01-23 16:49       ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
@ 2006-01-23 17:23         ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-24 08:33           ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2006-01-23 17:23 UTC (permalink / raw)
  To: ciprian.hodorogea@bitsp.com; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-admin

Ciprian Hodorogea <ciprian.hodorogea@bitsp.com> writes:
> This will put in the output SQL file postgres functions also (not only 
> those defined by me), for example:

> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
>      AS '$libdir/plpgsql', 'plpgsql_call_handler'
>      LANGUAGE c;

> CREATE TYPE tablefunc_crosstab_2 AS (
> 	row_name text,
> 	category_1 text,
> 	category_2 text
> );

> CREATE FUNCTION connectby(text, text, text, text, integer, text) RETURNS 
> SETOF record
>      AS '$libdir/tablefunc', 'connectby_text'
>      LANGUAGE c STABLE STRICT;


Those *were* all defined by you, at some point, because they aren't
preloaded in the system.  Perhaps you have forgotten running the
contrib-module installation scripts, but you evidently did.

If you are having difficulty in reloading the definitions it suggests
that your new installation is lacking the contrib modules.  You could
install them, or manually remove the unwanted stuff from the dump file,
or drop the functions from the source installation before making a dump.
(Some but not all of the contrib modules have "uninstall" scripts, which
would help with the last alternative.)

> Anyway, my question would be: if I have postgres 8.1.x installed on 
> computer A and the same version of postgres installed on computer B, 
> which would be your recommended way of backing up data on computer A and 
> put it on computer B?

pg_dump is certainly the preferred method.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: pg_dump - txt sql vs binary
  2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-23 14:35   ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-23 15:57     ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
  2006-01-23 16:49       ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-23 17:23         ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-01-24 08:33           ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-24 21:24             ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
  0 siblings, 1 reply; 10+ messages in thread

From: Ciprian Hodorogea @ 2006-01-24 08:33 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-admin

> 
> pg_dump is certainly the preferred method.
> 

I have Postgres 8.1.1 installed on a Windows machine and on a Linux 
machine. When I do pg_dump (from Windows) and then pg_restore from 
Linux, I get 52 errors, which I ignore and things seem to work fine, but 
I suppose something is wrong about this...

It is only the setup from the website that I have installed and not 
other contribs.

Best Regards, Ciprian



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: pg_dump - txt sql vs binary
  2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-23 14:35   ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-23 15:57     ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
  2006-01-23 16:49       ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-23 17:23         ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-24 08:33           ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
@ 2006-01-24 21:24             ` Michael Fuhr <mike@fuhr.org>
  2006-01-24 21:43               ` Re: pg_dump - txt sql vs binary Jaime Casanova <systemguards@gmail.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Michael Fuhr @ 2006-01-24 21:24 UTC (permalink / raw)
  To: Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin

On Tue, Jan 24, 2006 at 10:33:50AM +0200, Ciprian Hodorogea wrote:
> >pg_dump is certainly the preferred method.
> 
> I have Postgres 8.1.1 installed on a Windows machine and on a Linux 
> machine. When I do pg_dump (from Windows) and then pg_restore from 
> Linux, I get 52 errors, which I ignore and things seem to work fine, but 
> I suppose something is wrong about this...

As Tom mentioned, the Windows box apparently has some contributed
modules that the Linux box doesn't, so when you try to restore the
catalog entries on Linux you get errors.  If you know you don't
need those modules then you can ignore the errors; if you'd like
to have an error-free restore then either add the modules to Linux,
remove them from Windows, or delete them from the backup.  If you're
using pg_restore then you could use the -l/--list and -L/--use-list
options to omit certain objects from the restore.  See the documentation
for an example.

> It is only the setup from the website that I have installed and not 
> other contribs.

Did you build from source or did you install a pre-built package?
If from source then the contributed modules are in the contrib
directory; if from a package then look around for another package
that contains the contributed modules.

-- 
Michael Fuhr



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: pg_dump - txt sql vs binary
  2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-23 14:35   ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-23 15:57     ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
  2006-01-23 16:49       ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-23 17:23         ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
  2006-01-24 08:33           ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
  2006-01-24 21:24             ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
@ 2006-01-24 21:43               ` Jaime Casanova <systemguards@gmail.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Jaime Casanova @ 2006-01-24 21:43 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin

> On Tue, Jan 24, 2006 at 10:33:50AM +0200, Ciprian Hodorogea wrote:
> >
> > I have Postgres 8.1.1 installed on a Windows machine and on a Linux
> > machine. When I do pg_dump (from Windows) and then pg_restore from
> > Linux, I get 52 errors, which I ignore and things seem to work fine, but
> > I suppose something is wrong about this...
>
> > It is only the setup from the website that I have installed and not
> > other contribs.
>
> Did you build from source or did you install a pre-built package?
> If from source then the contributed modules are in the contrib
> directory; if from a package then look around for another package
> that contains the contributed modules.
>

when installing from the pg_installer
(http://pginstaller.projects.postgresql.org) it let you to install
contrib modules just selecting them (and easy way to do it... so they
appear to newbies as just other options in the server, and not as
contrib modules), also it defaults to install plpgsql in template1 and
let you install and activate (i don't know what it refers with
'activate') postgis and pl/java when selecting options for the
server...

it also install some pgadmin support functions by default...


just ignore warnings works for me because i don't use most of these
things, and for plpgsql i create it by hand in linux before run the
script generated by pg_dump

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2006-01-24 21:43 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-22 23:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 14:35   ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 15:56     ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 15:57     ` Michael Fuhr <mike@fuhr.org>
2006-01-23 16:49       ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 17:23         ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-24 08:33           ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-24 21:24             ` Michael Fuhr <mike@fuhr.org>
2006-01-24 21:43               ` Jaime Casanova <systemguards@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox