Re: pg_dump - txt sql vs binary

Ciprian Hodorogea <ciprian(dot)hodorogea(at)bitsp(dot)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