Thread: pg_dump excluding tables content but not table schema
I've some tables that are just cache. I'd like to just dump the table schema without dumping the table contend. I think I could do it in 2 steps but I'd like to avoid it. Is there a way? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
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.
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
Tom Lane wrote:
It's Postgres version 8.0.8
Thank you
Gastón Quiroga Allytech S.A.
Tom Lane wrote:
Gastón 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
=?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
Tom Lane wrote:
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 <tango@allytech.com> writes:It's Postgres version 8.0.8Well, 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
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 !
Adrian Klaver wrote:
Gastón Quiroga Allytech S.A.
Adrian Klaver wrote:
----- "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: Gastón <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 lanePer 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
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