[GENERAL] problem

[Alex Samad]

Hi

I setup a sync rep cluster 9.6

unfortunately I have made a bit of an issue for myself.

my backup site is full on the data partition, which i believe has lead for
my write site to be full on the pg_xlog partition - i believe that it is
holding logs to replicate and can't any more.

so now neither site will start 

1) how . what steps do I need to do to fix this - with out just adding
space !
2) how do I stop this from happening - i think the space being used up is
the repmgr stats page - i think - will not know till I can get the servers
back up

Alex

Re: [GENERAL] Begginers question

[Alex Samad]

On 16 August 2017 at 20:55, Achilleas Mantzios <ach...@matrix.gatewaynet.com
> wrote:

> On 16/08/2017 13:46, Alex Samad wrote:
>
>
>
> On 16 August 2017 at 16:16, Michael Paquier <michael.paqu...@gmail.com>
> wrote:
>
>> On Wed, Aug 16, 2017 at 2:32 PM, Alex Samad <a...@samad.com.au> wrote:
>> > 1) why did it fill up this time and not previously
>> > I add this
>> > archive_command = '/bin/true'
>> > wal_keep_segments = 1000 # <<< I'm guessing its this
>> >
>> > 2) how do I fix up, can I just remove the files from the pg_xlog
>> directory
>>
>
How can I work out how much space this is going to use,
I see in the comments a segment is 16M, so 1000 would have been 16G, So I
thought I would set it at 100 so 1.6G. but it ended up using about 4.5G. So
now I am confused


>
>> Don't do that. those files are managed by Postgres so you may finish
>> with a corrupted cluster. Instead you should lower the value of
>>
>
> Too late, its okay its a learning experience.
>
>
>> wal_keep_segments, reload the server parameters, and then enforce two
>> checkpoints to force WAL segments to be recycled. Note that this
>>
>
> how do I force check points
>
> checkpoint ;
> (the ; is not meant as a smiley or whatever )
>
>
>
>> depends also on the values of checkpoint_segments
>> (max_wal_size/min_wal_size in Postgres 9.5 and onwards).
>> --
>> Michael
>>
>
> thanks
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>

Re: [GENERAL] cluster question

[Alex Samad]

On 17 August 2017 at 10:51, Ian Barwick <ian.barw...@2ndquadrant.com> wrote:

> On 08/16/2017 02:41 PM, Alex Samad wrote:
> (...)
> >
> > okay think I have it setup, but when i do a switch over it gets stuck
> here.
> >
> >
> >
> > NOTICE: STANDBY PROMOTE successful
> > NOTICE: Executing pg_rewind on old master server
> > NOTICE: 3 files copied to /var/lib/pgsql/9.6/data
> > NOTICE: restarting server using '/usr/pgsql-9.6/bin/pg_ctl  -w -D
> /var/lib/pgsql/9.6/data -m fast restart'
> > pg_ctl: PID file "/var/lib/pgsql/9.6/data/postmaster.pid" does not exist
> > Is server running?
> > starting server anyway
> > NOTICE: STANDBY FOLLOW successful
>
> From the repmgr README:
>
> >> You must ensure that following a server start using `pg_ctl`, log output
> >> is not send to STDERR (the default behaviour). If logging is not
> configured,
> >> we recommend setting `logging_collector=on` in `postgresql.conf` and
> >> providing an explicit `-l/--log` setting in `repmgr.conf`'s
> `pg_ctl_options`
> >> parameter.
>
> i.e. when the old primary is restarted with:
>
> /usr/pgsql-9.6/bin/pg_ctl  -w -D /var/lib/pgsql/9.6/data -m fast
> restart
>
> the calling process hangs, waiting for logging output from pg_ctl.
> In "repmgr.conf" set "pg_ctl_options" to something like:
>
> pg_ctl_options='-l /path/to/log'
>
>
> Regards


Thanks, simple when you know, too many new things to look at



>
>
> Ian Barwick
>
> --
>  Ian Barwick   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [GENERAL] Begginers question

[Alex Samad]

Great I will add it to my notes.

Thanks

On 16 August 2017 at 20:55, Achilleas Mantzios <ach...@matrix.gatewaynet.com
> wrote:

> On 16/08/2017 13:46, Alex Samad wrote:
>
>
>
> On 16 August 2017 at 16:16, Michael Paquier <michael.paqu...@gmail.com>
> wrote:
>
>> On Wed, Aug 16, 2017 at 2:32 PM, Alex Samad <a...@samad.com.au> wrote:
>> > 1) why did it fill up this time and not previously
>> > I add this
>> > archive_command = '/bin/true'
>> > wal_keep_segments = 1000 # <<< I'm guessing its this
>> >
>> > 2) how do I fix up, can I just remove the files from the pg_xlog
>> directory
>>
>> Don't do that. those files are managed by Postgres so you may finish
>> with a corrupted cluster. Instead you should lower the value of
>>
>
> Too late, its okay its a learning experience.
>
>
>> wal_keep_segments, reload the server parameters, and then enforce two
>> checkpoints to force WAL segments to be recycled. Note that this
>>
>
> how do I force check points
>
> checkpoint ;
> (the ; is not meant as a smiley or whatever )
>
>
>
>> depends also on the values of checkpoint_segments
>> (max_wal_size/min_wal_size in Postgres 9.5 and onwards).
>> --
>> Michael
>>
>
> thanks
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>

Re: [GENERAL] Begginers question

[Alex Samad]

On 16 August 2017 at 16:16, Michael Paquier <michael.paqu...@gmail.com>
wrote:

> On Wed, Aug 16, 2017 at 2:32 PM, Alex Samad <a...@samad.com.au> wrote:
> > 1) why did it fill up this time and not previously
> > I add this
> > archive_command = '/bin/true'
> > wal_keep_segments = 1000 # <<< I'm guessing its this
> >
> > 2) how do I fix up, can I just remove the files from the pg_xlog
> directory
>
> Don't do that. those files are managed by Postgres so you may finish
> with a corrupted cluster. Instead you should lower the value of
>

Too late, its okay its a learning experience.


> wal_keep_segments, reload the server parameters, and then enforce two
> checkpoints to force WAL segments to be recycled. Note that this
>

how do I force check points


> depends also on the values of checkpoint_segments
> (max_wal_size/min_wal_size in Postgres 9.5 and onwards).
> --
> Michael
>

thanks

Re: [GENERAL] cluster question

[Alex Samad]

On 15 August 2017 at 16:35, Andreas Kretschmer <andr...@a-kretschmer.de>
wrote:

>
>
> Am 15.08.2017 um 05:15 schrieb Alex Samad:
>
>> Hi
>>
>> Quick question.  I have a 2 node cluster - each node has its own ip.
>>
>> But from reading this, I really need a 3rd ip, which potentially floats
>> between the nodes to which ever is the master / rw node.
>>
>> Is that right? Sort of makes sense I guess
>>
>>
> That's one way to solve that problem. If you are using repmgr/repmgrd you
> can call own commands on events like failover. And, if you are using
> pgbouncer or connection-pooling you can change the config for pgbouncer
> (redefine the databases and there connection strings) and reload pgbouncer.
> You can do that with the event-notification commands defined in your
> repmgr-config.
> Other solution: with some Java-drivers you can define several databases
> and checks (if the database read-only or rw), the next PostgreSQL 10 will
> offer a similar feature.
>
>
okay think I have it setup, but when i do a switch over it gets stuck here.



NOTICE: STANDBY PROMOTE successful
NOTICE: Executing pg_rewind on old master server
NOTICE: 3 files copied to /var/lib/pgsql/9.6/data
NOTICE: restarting server using '/usr/pgsql-9.6/bin/pg_ctl  -w -D
/var/lib/pgsql/9.6/data -m fast restart'
pg_ctl: PID file "/var/lib/pgsql/9.6/data/postmaster.pid" does not exist
Is server running?
starting server anyway
NOTICE: STANDBY FOLLOW successful


doesn't look like the output from https://github.com/
2ndQuadrant/repmgr/blob/master/README.md

NOTICE: STANDBY PROMOTE successful
NOTICE: Executing pg_rewind on old master server
NOTICE: 5 files copied to /var/lib/postgresql/9.5/data
NOTICE: restarting server using '/usr/local/bin/pg_ctl -w -D
/var/lib/postgresql/9.5/node_1/data -m fast restart'
pg_ctl: PID file "/var/lib/postgresql/9.5/node_1/data/postmaster.pid"
does not exist
Is server running?
starting server anyway
NOTICE: node 1 is replicating in state "streaming"
NOTICE: switchover was successful






> PS.: please don't top-posting.
>
>
>



> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Begginers question

[Alex Samad]

Hi

So I have been playing with an streaming  cluster. I have the replication
working I believe.

But whilst attempting to do an import of my original DB, I filled up my
disk pg_xlog directory.

Strangley I have tried this before and not filled this up.

so
1) why did it fill up this time and not previously
I add this
archive_command = '/bin/true'
wal_keep_segments = 1000 # <<< I'm guessing its this

2) how do I fix up, can I just remove the files from the pg_xlog directory

A

Re: [GENERAL] cluster question

[Alex Samad]

Hi

Quick question.  I have a 2 node cluster - each node has its own ip.

But from reading this, I really need a 3rd ip, which potentially floats
between the nodes to which ever is the master / rw node.

Is that right? Sort of makes sense I guess

A

On 14 August 2017 at 16:47, Andreas Kretschmer <andr...@a-kretschmer.de>
wrote:

> On 14 August 2017 08:39:54 GMT+02:00, Alex Samad <a...@samad.com.au>
> wrote:
> >Hi
> >
> >I have setup a streaming replicating cluster, with a hot standby.
> >
> >Now I would like to change the RW to hot standby and change the hot
> >standby
> >to be the RW server.
> >
> >Is it just a matter of updating recover.conf file ?
> >
> >Alex
>
> I would suggest you repmgr, with this tool you can do "repmgr standby
> switchover" to perform such tasks.
>
> Regards, Andreas.
>
>
> --
> 2ndQuadrant - The PostgreSQL Support Company
>

[GENERAL] cluster question

[Alex Samad]

Hi

I have setup a streaming replicating cluster, with a hot standby.

Now I would like to change the RW to hot standby and change the hot standby
to be the RW server.

Is it just a matter of updating recover.conf file ?

Alex

Re: [GENERAL] Question about loading up a table

[Alex Samad]

Hi

I don't have an extra 4T of filespace. I could potentially move the
attached lun from one server and attach to the other

well that was my question how to check if its pg_dump thats bound.  I have
checked network performance - 9.8Gb and I can write more data to disk

I do have 1 index

A


On 3 August 2017 at 02:11, Scott Marlowe <scott.marl...@gmail.com> wrote:

> On Tue, Aug 1, 2017 at 4:27 PM, Alex Samad <a...@samad.com.au> wrote:
> > Hi
> >
> > So just to go over what i have
> >
> >
> > server A (this is the original pgsql server 9.2)
> >
> > Server X and Server Y ... PGSQL 9.6 in a cluster - streaming replication
> > with hot standby.
> >
> >
> > I have 2 tables about 2.5T of diskspace.
> >
> > I want to get the date from A into X and X will replicate into Y.
> >
> >
> > I am currently on X using this command
> >
> > pg_dump -U  -h  -t BIGTABLE -a  | sudo -u postgres
> -i
> > psql -q ;
> >
> > This is taking a long time, its been 2 days and I have xfered around 2T..
> > This is just a test to see how long and to populate my new UAT env. so I
> > will have to do it again.
> >
> > Problem is time.  the pg_dump process is single threaded.
> > I have 2 routers in between A and X but its 10G networking - but my
> network
> > graphs don't show much traffic.
> >
> > Server X is still in use, there are still records being inserted into the
> > tables.
> >
> > How can I make this faster.
> >
> > I could shutdown server A and present the disks to server X, could I load
> > this up in PGSQL and do a table to table copy - i presume this would be
> > faster ... is this possible ?  how do I get around the same DB name ?
> > What other solutions do I have ?
>
> Yes, but if it's taking days to transfer 2TB then you need to
> investigate where your performance is tanking.
>
> Have you tried resyncing / scping files across the network to see how
> fast your network connection is?
>
> Have you tried just pg_dumping / restoring locally to get an idea how
> fast you can dump / restore withoout doing it over a network
> connection?
>
> Are you IO bound? Network bound? CPU bound?
>
> Is the destination copying data, or building indexes? Do you insert
> into a schema that already has indexes in place? If so have you tried
> dropping the indexes first and rebuilding them?
>

Re: [GENERAL] Question about loading up a table

[Alex Samad]

Hi

So just to go over what i have


server A (this is the original pgsql server 9.2)

Server X and Server Y ... PGSQL 9.6 in a cluster - streaming replication
with hot standby.


I have 2 tables about 2.5T of diskspace.

I want to get the date from A into X and X will replicate into Y.


I am currently on X using this command

pg_dump -U  -h  -t BIGTABLE -a  | sudo -u postgres -i
psql -q ;

This is taking a long time, its been 2 days and I have xfered around 2T..
This is just a test to see how long and to populate my new UAT env. so I
will have to do it again.

Problem is time.  the pg_dump process is single threaded.
I have 2 routers in between A and X but its 10G networking - but my network
graphs don't show much traffic.

Server X is still in use, there are still records being inserted into the
tables.

How can I make this faster.

I could shutdown server A and present the disks to server X, could I load
this up in PGSQL and do a table to table copy - i presume this would be
faster ... is this possible ?  how do I get around the same DB name ?
What other solutions do I have ?

Alex




On 1 August 2017 at 23:24, Scott Marlowe <scott.marl...@gmail.com> wrote:

> On Mon, Jul 31, 2017 at 11:16 PM, Alex Samad <a...@samad.com.au> wrote:
> > Hi
> >
> > I double checked and there is data going over, thought I would correct
> that.
> >
> > But it seems to be very slow.   Having said that how do I / what tools
> do I
> > use to check through put
>
> Try the pg_current_xlog_location function on the slave?
>

Re: [GENERAL] Question about loading up a table

[Alex Samad]

Hi

I double checked and there is data going over, thought I would correct that.

But it seems to be very slow.   Having said that how do I / what tools do I
use to check through put

A

On 1 August 2017 at 08:56, Alex Samad <a...@samad.com.au> wrote:

> Hi
>
> I'm using pg_dump 9.6 to do the dumps.
>
> I'm also pretty sure no data is being replicated until the end of the copy
> stdin as I was watching tcpdump output and I can see data from the orig
> master to the new master and no traffic between new master and the standby,
> pretty sure my replication is working as my other tables have replicated
> over.
>
>
> as for allow pg_dump to create copy stdin with specific number of rows -
> not sure what that is so hard / bad, if it was a option for somebody to
> use.  For my situation its sounds like a really good idea.
>
> I'm dumping a single table into a new single table so constraint shouldn't
> be a problem.
>
>
> Guess I have to just let it rung to completion
>
> Thanks
>
>
>
> On 1 August 2017 at 06:59, Scott Marlowe <scott.marl...@gmail.com> wrote:
>
>> On Mon, Jul 31, 2017 at 2:31 AM, vinny <vi...@xs4all.nl> wrote:
>> > On 2017-07-31 11:02, Alex Samad wrote:
>> >>
>> >> Hi
>> >>
>> >> I am using pg_dump | psql to transfer data from my old 9.2 psql into a
>> >> 9.6 psql.
>>
>> Note that you should be doing pg_dump with 9.6's pg_dump, as it's
>> possible for 9.2's pg_dump to not know about a 9.6 feature.
>>
>> >> The new DB server is setup as master replicating to a hot standby
>> >> server.
>> >>
>> >> What I have noticed is that the rows don't get replicated over until
>> >> the copy from stdin is finished...  hard to test when you have M+ lines
>> >> of rows.
>>
>> SNIP
>>
>> >> Is there a way to tell the master to replicate earlier
>> >
>> > I highly doubt it, because the master cannot know what to replicate
>> until
>> > your transaction is ended with a COMMIT. If you end with ROLLBACK,
>> > or your last query is DELETE FROM (your_table>;
>> > then there isn't even anything to replicate at all...
>>
>> This is actually a visibility issue. All the new changes are
>> replicated to the slave, but just like on the master, other
>> connections can't see the change because it's not visible. The slave,
>> except for some small delay (seconds etc) is an exact replica of the
>> master. So even a delete at the end gets replicated. You just don't
>> see anything but possible table bloat to show for it.
>>
>> To prove this to oneself, start the copy, and get into another session
>> to the master. You don't see any rows there either until the commit
>> after the copy.
>>
>
>

Re: [GENERAL] partitioning question

[Alex Samad]

The comparison would be

a if then else end if .. about 8 of them 2013-> and a static insert into

v's

making a dynamic string and using execute, my presumption would be the
execute would be expensive verses a INSERT command

A

On 1 August 2017 at 07:04, Scott Marlowe <scott.marl...@gmail.com> wrote:

> On Sun, Jul 30, 2017 at 7:13 PM, Alex Samad <a...@samad.com.au> wrote:
> > How expensive is dynamic over static.  I'm looking at storing yearly
> now, so
> > I figure if my if then clause has the latest year at the top it should be
> > very quick.
>
> Assuming you're not doing anything particularly crazy it's minimal.
> But what is good performance for one application may not be acceptable
> for others. Generally the cost of inserting is MUCH higher than the
> cost of dynamically setting the target, esp if you stick to plpgsql
> and don't try to use rules to accomplish it.
>

Re: [GENERAL] Question about loading up a table

[Alex Samad]

Hi

I'm using pg_dump 9.6 to do the dumps.

I'm also pretty sure no data is being replicated until the end of the copy
stdin as I was watching tcpdump output and I can see data from the orig
master to the new master and no traffic between new master and the standby,
pretty sure my replication is working as my other tables have replicated
over.


as for allow pg_dump to create copy stdin with specific number of rows -
not sure what that is so hard / bad, if it was a option for somebody to
use.  For my situation its sounds like a really good idea.

I'm dumping a single table into a new single table so constraint shouldn't
be a problem.


Guess I have to just let it rung to completion

Thanks



On 1 August 2017 at 06:59, Scott Marlowe <scott.marl...@gmail.com> wrote:

> On Mon, Jul 31, 2017 at 2:31 AM, vinny <vi...@xs4all.nl> wrote:
> > On 2017-07-31 11:02, Alex Samad wrote:
> >>
> >> Hi
> >>
> >> I am using pg_dump | psql to transfer data from my old 9.2 psql into a
> >> 9.6 psql.
>
> Note that you should be doing pg_dump with 9.6's pg_dump, as it's
> possible for 9.2's pg_dump to not know about a 9.6 feature.
>
> >> The new DB server is setup as master replicating to a hot standby
> >> server.
> >>
> >> What I have noticed is that the rows don't get replicated over until
> >> the copy from stdin is finished...  hard to test when you have M+ lines
> >> of rows.
>
> SNIP
>
> >> Is there a way to tell the master to replicate earlier
> >
> > I highly doubt it, because the master cannot know what to replicate until
> > your transaction is ended with a COMMIT. If you end with ROLLBACK,
> > or your last query is DELETE FROM (your_table>;
> > then there isn't even anything to replicate at all...
>
> This is actually a visibility issue. All the new changes are
> replicated to the slave, but just like on the master, other
> connections can't see the change because it's not visible. The slave,
> except for some small delay (seconds etc) is an exact replica of the
> master. So even a delete at the end gets replicated. You just don't
> see anything but possible table bloat to show for it.
>
> To prove this to oneself, start the copy, and get into another session
> to the master. You don't see any rows there either until the commit
> after the copy.
>

[GENERAL] Question about loading up a table

[Alex Samad]

Hi

I am using pg_dump | psql to transfer data from my old 9.2 psql into a 9.6
psql.

The new DB server is setup as master replicating to a hot standby server.

What I have noticed is that the rows don't get replicated over until the
copy from stdin is finished... hard to test when you have M+ lines of rows.

Is there a way to tell the master to replicate earlier or is there a way to
get pg_dump to bundle into say 100K rows at a time ?

Thanks
Alex

Re: [GENERAL] vacuum on streaming replication

[Alex Samad]

Thanks

On 31 July 2017 at 18:11, Chris Travers  wrote:

>
>
> On Mon, Jul 31, 2017 at 10:08 AM, Michael Paquier <
> michael.paqu...@gmail.com> wrote:
>
>> On Mon, Jul 31, 2017 at 7:28 AM, Andreas Kretschmer
>>  wrote:
>> > The standby is read only, vacuum runs on the master and replicated to
>> the standby. Analyse as well.
>>
>> Please note as well that if hot_standby_feedback is enabled, the
>> cleanup done by VACUUM on the primary is influenced as well so as
>> tuples that a standby may need to avoid conflicts for its transactions
>> are not removed. So VACUUM may result in less cleanup depending on the
>> read load on the standby.
>>
>
> Also that replication slots provide standby feedback and may further delay
> vacuuming when the standby is offline.
>
>> --
>> Michael
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> Best Wishes,
> Chris Travers
>
> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
> lock-in.
> http://www.efficito.com/learn_more
>

[GENERAL] vacuum on streaming replication

[Alex Samad]

Hi

setup a cluster, with streaming replication and hot stand by

the idea is to use the stand by to do queries whilst the primary is doing
inserts.

But I noticed the stats on the stand by server don't update, nor can I run
vacuum against it as its in recovery mode.

So how do update the stats and how will it affect my queries ?

Re: [GENERAL] partitioning question

[Alex Samad]

How expensive is dynamic over static.  I'm looking at storing yearly now,
so I figure if my if then clause has the latest year at the top it should
be very quick.




On 31 July 2017 at 11:07, Justin Pryzby <pry...@telsasoft.com> wrote:

> On Mon, Jul 31, 2017 at 10:25:54AM +1000, Alex Samad wrote:
> > I note that you link to P10 and I am currently looking at 9.6.  The
> changes
> > do look nice for partitioning for p10.
>
> Yes sorry, pg10 is beta - avoid using it except for testing purposes.
>
> > I will add currently we don't delete anything, we will keep adding to it.
> >
> > Also I am thinking my insert trigger becomes a lot smaller and easier if
> I
> > leave it at yearly.
> Note: the trigger function can either be a static function updated monthly
> (to
> handle the next month), preferably with the most recent months tested
> first (so
> a typical newly-inserted rows only goes through one if/case test).
>
> Alternately, the trigger function can dynamically compute the table into
> which
> to insert using plpgsql "format()" similar to here:
> https://www.postgresql.org/docs/9.1/static/plpgsql-
> statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE
>
> Justin
>

Re: [GENERAL] partitioning question

[Alex Samad]

Hi

I note that you link to P10 and I am currently looking at 9.6.  The changes
do look nice for partitioning for p10.


Interesting your suggest that the MM parition isn't that bad.

I will add currently we don't delete anything, we will keep adding to it.

Also I am thinking my insert trigger becomes a lot smaller and easier if I
leave it at yearly.

Also thinking if P10 was the current recommended version right now I would
probably look at MM because it looks like it makes partitioning easier

Alex



On 31 July 2017 at 09:54, Justin Pryzby <pry...@telsasoft.com> wrote:

> On Mon, Jul 31, 2017 at 09:15:29AM +1000, Alex Samad wrote:
> > Hi
> >
> > I was about to partition a large (?) approx 3T of data 2B rows into
> > partition tables but broken up into MM ...
> >
> > Now I have been reading about limiting the number of partitions otherwise
> > it could slow down the parser.
> >
> > My reasoning for limiting to MM was that most of the request would be
> > monthly based.
> >
> > Should I be making the partitioning based on  instead and have lots
> > more indexs.
> >
> > If I have an index on the timestamp field will it help limiting to
> MM ?
>
> The major advantages of partitions are enumerated here:
> https://www.postgresql.org/docs/10/static/ddl-partitioning.html#ddl-
> partitioning-overview
>
> For your case, it might be that seq scans of an entire "monthly" partition
> turn
> out to be very advantageous, compared with index scan (or seq scan of
> entire
> 3TB data).
>
> Also DROPing the oldest partition every month is commonly very much more
> efficient than DELETEing it..
>
> There are warnings like these:
>
> |All constraints on all partitions of the master table are examined during
> |constraint exclusion, so large numbers of partitions are likely to
> increase
> |query planning time considerably. Partitioning using these techniques
> will work
> |well with up to perhaps a hundred partitions; don't try to use many
> thousands
> |of partitions.
>
> Unless you have 100s of years of data I don't think it would be a problem.
>
> For us, having hundreds of partitions hasn't been an issue (planning time
> is
> insignificant for our analytic report queries).  But there's an overhead to
> partitions and at some point the cost becomes significant.  (Actually, I
> think
> one cost which *did* hit us, while experimenting with *daily* partition
> granularity of every table, was probably due to very large pg_statistics
> and
> pg_attributes tables, which no longer fit in buffer cache).
>
> Justin
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] partitioning question

[Alex Samad]

Hi

I was about to partition a large (?) approx 3T of data 2B rows into
partition tables but broken up into MM ...

Now I have been reading about limiting the number of partitions otherwise
it could slow down the parser.

My reasoning for limiting to MM was that most of the request would be
monthly based.

Should I be making the partitioning based on  instead and have lots
more indexs.

If I have an index on the timestamp field will it help limiting to MM ?


A

Re: [GENERAL] Question about paritioning

[Alex Samad]

so I have a pgsql function that will create tables based on MD for x
years in advance and inherits of base table.
with this
CHECK (_received >= '2013-01-01 00:00:00+11'::timestamp with time zone AND
_received < '2013-02-01 01:00:00+11'::timestamp with time zone)

now for the insert, do I create / update this monthly have had a 2 or 3
level if then check before inserting

or do I create a programatic insert that works out the table name

On 27 July 2017 at 18:36, John R Pierce <pie...@hogranch.com> wrote:

> On 7/27/2017 12:43 AM, Alex Samad wrote:
>
>>
>> ... as long as the queries stay on a small amount of parts that we should
>> be okay.
>>
>
> thats true as long as the planner can restrict the queries to the correct
> partition...  but there's lots of queries that end up having to hit all
> partitions because the planner can't guess correctly.
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Question about paritioning

[Alex Samad]

So is date_trunc better than to_char ? I'm thinking it probably is

as for the number of partitions, well we don't plan on deleting anything,
but from my reading as long as the queries stay on a small amount of parts
that we should be okay.

A

On 27 July 2017 at 15:33, John R Pierce <pie...@hogranch.com> wrote:

> On 7/26/2017 10:08 PM, Alex Samad wrote:
>
>> I have a large table about 3B rows, that I would like to partition on a
>> column called _received which is  of type timestamp
>>
>>
> a good goal is to have no more than about 100 partitions max, and ideally
> more like 25.
>
> when we partition on time stamp, we typically do it by the week, as we're
> doing 6 month data retention.
>
> IIRC, we're using DATE_TRUNC('week', timestamp)::DATE  for use as the
> partition label and key.
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Question about paritioning

[Alex Samad]

Hi

I have a large table about 3B rows, that I would like to partition on a
column called _received which is  of type timestamp

I followed this
https://medium.com/@StreamBright/creating-partitions-automatically-in-postgresql-7006d68c0fbb


and came up with
CREATE OR REPLACE FUNCTION create_partition_and_insert() RETURNS trigger AS
  $BODY$
DECLARE
  partition_date TEXT;
  partition TEXT;
BEGIN
  partition_date := to_char(NEW._received,'MM');
  partition := TG_TABLE_NAME || '_' || partition_date;
  IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition)
THEN
RAISE NOTICE 'A partition has been created %',partition;
EXECUTE 'CREATE TABLE ' || partition || ' (check (
to_char(_received, '''MM''') = ''' || partition_date || ''')) INHERITS
(' || TG_TABLE_NAME || ');';
  END IF;
  EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_TABLE_NAME ||
' ' || quote_literal(NEW) || ').* RETURNING patent_id;';
  RETURN NULL;
END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


Which I believe generates something like this
CREATE TABLE feedintra_201707( check (to_char(_received,'MM') =
'201707' )) INHERITS (XX);

My concern is the cost of doing to_char on every row.

So
Is this going to work
Is this the right way to do it.

I was thinking if I could change the check to be something like

check ( _recieved >= MM 1 00:00 and _recieved <= MM  23:59:59.999 )

so I am not sure how to make up the above line based on a timestamp

Thanks
Alex