Обсуждение: --//pgsql partitioning-///--------------------
hi all,
I have alot data that I have to isert them to db,...
Now I decide to use of partitioning
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
but problem here is how I can do something that when I insert data, the master table remains empty
and just data inserted into inherited tables.
Thanks
--
Shahrzad Khorrami
I have alot data that I have to isert them to db,...
Now I decide to use of partitioning
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
but problem here is how I can do something that when I insert data, the master table remains empty
and just data inserted into inherited tables.
Thanks
--
Shahrzad Khorrami
shahrzad khorrami wrote: > hi all, > > I have alot data that I have to isert them to db,... > Now I decide to use of partitioning > http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html > but problem here is how I can do something that when I insert data, > the master table remains empty > and just data inserted into inherited tables. > and this is the way partitioning works... foo > Thanks > -- > Shahrzad Khorrami
On Wed, Nov 4, 2009 at 4:49 AM, shahrzad khorrami <shahrzad.khorrami@gmail.com> wrote: > but problem here is how I can do something that when I insert data, the > master table remains empty > and just data inserted into inherited tables. > Here are two (not necessarily mutually exclusive) options for you: 1) use triggers/rules on the master table and do all your inserts directed to it and have the trigger/rule do the right thing 2) write your insert/update/delete statements to use the correct inherited table directly, and write a trigger on the master that denies any inserts. Did you read the documentation on partitions? It covers this.
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Vick Khera > Here are two (not necessarily mutually exclusive) options for you: >1) use triggers/rules on the master table and do all your inserts >directed to it and have the trigger/rule do the right thing >2) write your insert/update/delete statements to use the correct >inherited table directly, and write a trigger on the master that >denies any inserts. #2 would have better performance than #1. Use #1 only if you have no choice.
Hi everyone, I have successfully been doing text based backups and restores to a few servers for reporting and testing purposes for a few years now. Due to growth in the database I am switching to the native format backup and using pg_restore to restore the database. The problem I've run into is that at least one table is inaccessible after the restore finishes. Any queries to that table just hang. Other tables seem to work ok. I have no idea why. Here's my backup command: /usr/bin/pg_dump -U postgres rms-prod -F c -f $WALPATH/rms-prod.pgc Here's my restore command: pg_restore -v -U postgres -h $PGHOST --exit-on-error -d rms-prod $WALPATH/rms-prod.pgc However, when I do a text based backup and subsequent restore, everything works just fine. I'm currently running 8.3.7 on all my servers. Thanks for the help. Scot Kreienkamp skreien@la-z-boy.com
"Scot Kreienkamp" <SKreien@la-z-boy.com> writes: > I have successfully been doing text based backups and restores to a few > servers for reporting and testing purposes for a few years now. Due to > growth in the database I am switching to the native format backup and > using pg_restore to restore the database. The problem I've run into is > that at least one table is inaccessible after the restore finishes. Any > queries to that table just hang. Other tables seem to work ok. I have > no idea why. That's pretty bizarre, because one of the standard test procedures we use is to check that the SQL emitted by pg_restore from an -Fc backup is exactly the same as a text-mode dump. I have to think there's something else you did differently. Have you looked into pg_locks to see if there's a lock blocking your query? Have you tried comparing EXPLAIN results to see if the query plan is the same? (If not, maybe you forgot an ANALYZE step?) regards, tom lane
"Scot Kreienkamp" <SKreien@la-z-boy.com> writes: > I have successfully been doing text based backups and restores to a few > servers for reporting and testing purposes for a few years now. Due to > growth in the database I am switching to the native format backup and > using pg_restore to restore the database. The problem I've run into is > that at least one table is inaccessible after the restore finishes. Any > queries to that table just hang. Other tables seem to work ok. I have > no idea why. That's pretty bizarre, because one of the standard test procedures we use is to check that the SQL emitted by pg_restore from an -Fc backup is exactly the same as a text-mode dump. I have to think there's something else you did differently. Have you looked into pg_locks to see if there's a lock blocking your query? Have you tried comparing EXPLAIN results to see if the query plan is the same? (If not, maybe you forgot an ANALYZE step?) regards, tom lane [Scot Kreienkamp] It's definitely not locks. I'm exercising this on a sandbox server that I'm the only one that has access to it. And it's not a complex query by any means. It's a simple select query. Here's the query on our production server: psql -U postgres -d rms-prod -c "explain select * from soldtrx" -h rets5000 QUERY PLAN -------------------------------------------------------------------- Seq Scan on soldtrx (cost=0.00..223945.51 rows=4833151 width=276) (1 row) Here's the query in the sandbox: psql -U postgres -d rms-prod -c "explain select * from soldtrx" QUERY PLAN -------------------------------------------------------------------- Seq Scan on soldtrx (cost=0.00..223459.51 rows=4833151 width=278) (1 row) Another strange thing I just found when composing this email... If I set limit 5 on the query it works on the soldtrx table, but if I don't set a limit it just hangs. Thanks, Scot Kreienkamp skreien@la-z-boy.com
On Thu, Nov 05, 2009 at 10:41:54AM -0500, Scot Kreienkamp wrote: > Here's the query in the sandbox: > psql -U postgres -d rms-prod -c "explain select * from soldtrx" > QUERY PLAN > -------------------------------------------------------------------- > Seq Scan on soldtrx (cost=0.00..223459.51 rows=4833151 width=278) > (1 row) > > Another strange thing I just found when composing this email... If I set > limit 5 on the query it works on the soldtrx table, but if I don't set a > limit it just hangs. That's to be expected; it's trying to copy all 5 million rows into the psql process and that'll take a while. I presume you weren't trying a similar test on the original box as you'd have got exactly the same thing. Running COUNT(*) on the table is probably a better way to make sure you've got all the rows you'd expect in there as you'll only have to copy a single row over to psql--it'll still take a while for PG to churn through things though. iostat or vmstat are good tools to see what the database box is doing while it's working. -- Sam http://samason.me.uk/
"Scot Kreienkamp" <SKreien@la-z-boy.com> writes: > Another strange thing I just found when composing this email... If I set > limit 5 on the query it works on the soldtrx table, but if I don't set a > limit it just hangs. Is it chewing CPU, chewing I/O, or just sitting? If the latter, try attaching to the backend with gdb and getting a stack trace. I'm still not buying the theory that the dump/restore method has something to do with it. You might try the test yourself: run pg_restore's output into a text file and compare with the text dump. regards, tom lane
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sam Mason Sent: Thursday, November 05, 2009 10:50 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_dump native format will not restore correctly On Thu, Nov 05, 2009 at 10:41:54AM -0500, Scot Kreienkamp wrote: > Here's the query in the sandbox: > psql -U postgres -d rms-prod -c "explain select * from soldtrx" > QUERY PLAN > -------------------------------------------------------------------- > Seq Scan on soldtrx (cost=0.00..223459.51 rows=4833151 width=278) > (1 row) > > Another strange thing I just found when composing this email... If I set > limit 5 on the query it works on the soldtrx table, but if I don't set a > limit it just hangs. That's to be expected; it's trying to copy all 5 million rows into the psql process and that'll take a while. I presume you weren't trying a similar test on the original box as you'd have got exactly the same thing. Running COUNT(*) on the table is probably a better way to make sure you've got all the rows you'd expect in there as you'll only have to copy a single row over to psql--it'll still take a while for PG to churn through things though. iostat or vmstat are good tools to see what the database box is doing while it's working. [Scot Kreienkamp] I guess I could buy that, but why would it show up suddenly in one night? We've never had a problem with this prior to last night. Otherwise our reporting would have found it. Those queries didn't change from yesterday to today. Thanks, Scot Kreienkamp skreien@la-z-boy.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sam Mason Sent: Thursday, November 05, 2009 10:50 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_dump native format will not restore correctly On Thu, Nov 05, 2009 at 10:41:54AM -0500, Scot Kreienkamp wrote: > Here's the query in the sandbox: > psql -U postgres -d rms-prod -c "explain select * from soldtrx" > QUERY PLAN > -------------------------------------------------------------------- > Seq Scan on soldtrx (cost=0.00..223459.51 rows=4833151 width=278) > (1 row) > > Another strange thing I just found when composing this email... If I set > limit 5 on the query it works on the soldtrx table, but if I don't set a > limit it just hangs. That's to be expected; it's trying to copy all 5 million rows into the psql process and that'll take a while. I presume you weren't trying a similar test on the original box as you'd have got exactly the same thing. Running COUNT(*) on the table is probably a better way to make sure you've got all the rows you'd expect in there as you'll only have to copy a single row over to psql--it'll still take a while for PG to churn through things though. iostat or vmstat are good tools to see what the database box is doing while it's working. [Scot Kreienkamp] I guess I could buy that, but why would it show up suddenly in one night? We've never had a problem with this prior to last night. Otherwise our reporting would have found it. Those queries didn't change from yesterday to today. Thanks, Scot Kreienkamp skreien@la-z-boy.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
I'm still not buying the theory that the dump/restore method has something to do with it. You might try the test yourself: run pg_restore's output into a text file and compare with the text dump. regards, tom lane Haven't figured out exactly why yet, but I did find the difference as to why text based restore works for me but native format doesn't. In the text based restore permissions are granted to all restored tables and the schema itself. Using the native format permissions are granted to restored tables, but NOT the schema, which makes all the tables in the restored schema inaccessible to all but the postgres user. This is of course when restoring only the schema and its contents, not the entire database. My text based method does a dump to a file from the production database immediately prior to the restore, then cats the file to psql for the restore. My native format restore I'm simply restoring from last nights native format backup of the database and using the schema only switch to pg_restore. Not sure if that makes the difference or not. Thanks for the help in trying to figure this out. I'm still looking into it. Scot Kreienkamp