Обсуждение: --//pgsql partitioning-///--------------------

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