pg_dump excluding tables content but not table schema

On 28/12/2009 18:41, Ivan Sergio Borgonovo wrote:

I'd like to just dump the table schema without dumping the table
contend.

pg_dump -s -t <table name> <db name>

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On Mon, 28 Dec 2009 19:39:36 +0000
Raymond O'Donnell <rod@iol.ie> wrote:

On 28/12/2009 18:41, Ivan Sergio Borgonovo wrote:

I'd like to just dump the table schema without dumping the table
contend.

pg_dump -s -t <table name> <db name>

My fault. I was not clear enough.
I'd like to make a "mostly" full backup, excluding from backup just
the data of some tables but backing up the schema of those tables.

mmm let me try if

pg_dump -Fc -Z9 -s -t *.cache* -d mydb > schema_only.bak
pg_dump -Fc -Z9 -T *.cache* -d mydb > nearly_full.bak

cat nearly_full.bak schema_only.bak | pg_restore -1 -d mydb

It seems it is working... I'll test if everything is there.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On 28/12/2009 20:20, Ivan Sergio Borgonovo wrote:

On Mon, 28 Dec 2009 19:39:36 +0000
Raymond O'Donnell <rod@iol.ie> wrote:

On 28/12/2009 18:41, Ivan Sergio Borgonovo wrote:

I'd like to just dump the table schema without dumping the table
contend.

pg_dump -s -t <table name> <db name>

My fault. I was not clear enough.
I'd like to make a "mostly" full backup, excluding from backup just
the data of some tables but backing up the schema of those tables.

mmm let me try if

pg_dump -Fc -Z9 -s -t *.cache* -d mydb > schema_only.bak
pg_dump -Fc -Z9 -T *.cache* -d mydb > nearly_full.bak

cat nearly_full.bak schema_only.bak | pg_restore -1 -d mydb

It seems it is working... I'll test if everything is there.

I don't think you can do it in one step with a single invocation of
pg_dump - I reckon what you've come up with there is the way to go.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Hi:
I cannot dump any database from my server. When I try to do it i
receive this error

pg_dump: The command was: SELECT tableoid, oid, nspname, (select usename
from pg_user where nspowner = usesysid) as usename, nspacl FROM pg_namespace

I checked the pg_user database and I found this:

postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd |
passwd | valuntil | useconfig
---------------+----------+-------------+----------+-----------+----------+----------+-----------
postgres | 1 | t | t | t |
******** | |
postgres | 1 | t | t | t |
******** | |
administrador | 100 | t | t | t |
******** | |
user1 | 101 | t | t | t |
******** | |
user2 | 102 | f | f | f |
******** | |

Anyone can tell me from where should I start?. This is a production
server.

Regards.

--
Gast�n Quiroga

=?ISO-8859-1?Q?Gast=F3n_Quiroga?= <gastonq@allytech.com> writes:

I checked the pg_user database and I found this:

postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd |
passwd | valuntil | useconfig
---------------+----------+-------------+----------+-----------+----------+----------+-----------
postgres | 1 | t | t | t |
******** | |
postgres | 1 | t | t | t |
******** | |
administrador | 100 | t | t | t |
******** | |

What PG version is that? (Apparently pre-8.1, but what exactly?)

It would be useful to look at the underlying table:

select ctid,xmin,xmax,* from pg_shadow;

regards, tom lane

Thanks for your reply Tom, and sorry for the duplicated e-mails.

It's Postgres version 8.0.8

Thank you

Gast�n Quiroga
Allytech S.A.

Tom Lane wrote:

=?ISO-8859-1?Q?Gast=F3n?= <tango@allytech.com> writes:

It's Postgres version 8.0.8

Well, that's pretty ancient, and I see at least one bug in the release
history that could result in duplicated rows. I'd counsel an update to
8.0.something-recent. You can probably delete the extra row using
a WHERE on ctid.

regards, tom lane

Thank You Tom:
I'll Try to make an update, but the 2 fields are equals row by row,
how could I make a difference in the "WHERE" statement?

Regards

Gast�n Quiroga
Allytech S.A.

Tom Lane wrote:

Gast�n Quiroga wrote:

Thank You Tom:
I'll Try to make an update, but the 2 fields are equals row by
row, how could I make a difference in the "WHERE" statement?

Use the ctid hidden system field.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

----- "Gastón Quiroga" <gastonq@allytech.com> wrote:

Thank You Tom:
I'll Try to make an update, but the 2 fields are equals row by row,
how could I make a difference in the "WHERE" statement?

Regards

Gastón Quiroga
Allytech S.A.

Tom Lane wrote:

=?ISO-8859-1?Q?Gast=F3n?= <tango@allytech.com> writes:

It's Postgres version 8.0.8 Well, that's pretty ancient, and I see at
least one bug in the release
history that could result in duplicated rows. I'd counsel an update
to
8.0.something-recent. You can probably delete the extra row using
a WHERE on ctid.

regards, tom lane

Per Toms previous post use the following query:
select ctid,xmin,xmax,* from pg_shadow;

Then use the ctid value of the duplicate value in the where clause.

Adrian Klaver
aklaver@comcast.net

The upgrade works, thank you all !

Gastón Quiroga
Allytech S.A.

Adrian Klaver wrote:

On Mon, 28 Dec 2009 21:20:17 +0100
Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

pg_dump -Fc -Z9 -s -t *.cache* -d mydb > schema_only.bak
pg_dump -Fc -Z9 -T *.cache* -d mydb > nearly_full.bak

cat nearly_full.bak schema_only.bak | pg_restore -1 -d mydb

It seems it is working... I'll test if everything is there.

Unfortunately it doesn't work as expected.
It silently skip to restore the second backup (schema_only.bak).
I'm surprised it didn't output any error message, but the cache
tables aren't there.

It seems that you have to actually restore the 2 backup separately.

pg_restore -1 -d mydb < nearly_full.bak
pg_restore -1 -d mydb < schema_only.bak

I can't think of any other way to restore both in one transaction
unless I backup in plain text. But that should have other drawback.

Any hint?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Ivan Sergio Borgonovo <mail@webthatworks.it> writes:

It seems that you have to actually restore the 2 backup separately.

pg_restore -1 -d mydb < nearly_full.bak
pg_restore -1 -d mydb < schema_only.bak

I can't think of any other way to restore both in one transaction
unless I backup in plain text. But that should have other drawback.

Any hint?

In general what I do is backup it all then filter at restore time,
editing the restore catalog (see pg_restore options -l and
-L).

Incidentally I've written code for automating this for me, that's called
pg_staging and is available at github and already is in debian (I miss a
source release so that RPM will follow):

http://github.com/dimitri/pg_staging

Regards,
--
dim