PG_DUMP Issues

[gwinn7]

Hello,

I have been wrestling with pg_dump.

PG_DUMP ISSUE #1
Has anyone got -T option to work on 8.x of PostgreSQL? I can't get this to work.

-T option is supposed to exclude any specified table from your pg_dump output. Example script...

pg_dump mydatabase -n myschema -T mytable1 -U myuser > mytextfile.bak

Has anyone got this to work? I don't know if I am doing anything wrong and am trying to follow the documentation.

PG_DUMP ISSUE #2
Has anyone had problems generating a pg_dump where the primary key constraint was missing from one of your tables, but in the source database, the primary key is there?

PG_DUMP ISSUE #3
Has anyone had a problem where you did a restore from a PG_DUMP script with trigger functions that failed to work after import? I am getting an error that the function is failing, but when I resubmit the trigger function to the system, it works fine.

Gary
gwinn7

 

[gwinn7]

Update RE #3

The problem was caused by a misunderstanding of how pg_dump exports to the file. Unfortunately, the formatting of functions are not preserved by default. So a function looking something like this...

CREATE OR REPLACE FUNCTION myfunc() RETURNS trigger as
$$
BEGIN
if now() then
-- process here...
end if;
END;
$$ LANGUAGE 'plpgsql';

Comes out something like this...

CREATE OR REPLACE FUNCTION myfunc() RETURNS trigger as
$$ BEGIN if now() then -- process here... end if; END;
$$ LANGUAGE 'plpgsql';

The problem with the above statement is the comment "--" comments out the remaining code.

The way to avoid this is to use another formatting option. I used the following...

pg_dump mydatabase -n myschema -s -Fc -U postgres -f c:/testformat.txt

To Restore the same file I used the following...

pg_restore -Fc -d mynewdatabase c:/testformat.txt -U postgres

For the data, I did not have to use the -Fc option.

Hope this helps anyone learning these backup and restore features.

Feel free to contribute if your experience differs.

Thanks,

Gary
gwinn7

 

[thewhistler1]

PG_DUMP ISSUE #1

If I'm not mistaken the -t option doesn't exclude the table but only dumps that one table.

Example:
If I one want the customer table from the sales db I would do this:

pg_dump sales -t customer >'/tmp/cust.dmp'

TT

 

[gwinn7]

Whistler,

Thank you for responding. The -t option does exclusively export one table, but according to the --help, it also says -T (upper case) excludes table(s).

My post previously says "-T".

Thanks again,

Gary
gwinn7