Thread: Streaming Questions and Sizing

On 06/30/2015 09:14 PM, Andy Erskine wrote:
> I have db of 123GB Currently and this is streaming to a secondary DB
> which also shows a size of 123GB (Using pgAdmin)
>
> The db's both reside on a 250GB directorys and on the Master i'm using
> 60% of capacity which seems expected
> On the secondary i am using 88% of the disks capacity. I assume this is
> something to do with the WAL segments ? which is currently
> wal_keep_segments = 500

That is for the server sending the segments, so I would assume you are
getting this number from the master not the standby?

>
> If this is the issue how do i go about tuning this to what is required ?
>
> If i make the segments to small then a restore may end up with an un
> synchronised database right ?
>
> thanks.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 07/01/2015 06:24 AM, Andy Erskine wrote:

CCing list

> Yes that's configured on the master

So the segments would pile up there not on the standby. You do not say
what OS you are using, but if it is a Unixen variation, then run:

du -h

on the master and standby directories to see where the capacity is being
used.

>
> On 1 Jul 2015 11:20 pm, "Adrian Klaver" <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 06/30/2015 09:14 PM, Andy Erskine wrote:
>
>         I have db of 123GB Currently and this is streaming to a secondary DB
>         which also shows a size of 123GB (Using pgAdmin)
>
>         The db's both reside on a 250GB directorys and on the Master i'm
>         using
>         60% of capacity which seems expected
>         On the secondary i am using 88% of the disks capacity. I assume
>         this is
>         something to do with the WAL segments ? which is currently
>         wal_keep_segments = 500
>
>
>     That is for the server sending the segments, so I would assume you
>     are getting this number from the master not the standby?
>
>
>         If this is the issue how do i go about tuning this to what is
>         required ?
>
>         If i make the segments to small then a restore may end up with an un
>         synchronised database right ?
>
>         thanks.
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 07/01/2015 05:04 PM, Andy Erskine wrote:
> the cmd you listed did work it didn't like the -d option
>
> however on the master :
>
> data]$ du -h
> 7.9M    ./pg_clog
> 168K    ./pg_subtrans
> 232K    ./pg_multixact/members
> 120K    ./pg_multixact/offsets
> 356K    ./pg_multixact
> 12K     ./pg_notify
> 1.9G    ./pg_log
> 127G    ./base/16385
> 6.3M    ./base/12865
> 4.0K    ./base/pgsql_tmp
> 6.3M    ./base/1
> 6.4M    ./base/12870
> 127G    ./base
> 764K    ./pg_stat_tmp
> 4.0K    ./pg_tblspc
> 364K    ./pg_xlog/archive_status
> 8.1G    ./pg_xlog
> 2.2M    ./global
> 4.0K    ./pg_serial
> 4.0K    ./pg_snapshots
> 4.0K    ./pg_twophase
> 137G    .
>
> And the secondary :
>
> data]$ du -h
> 6.4M    ./base/12870
> 4.0K    ./base/pgsql_tmp
> 6.3M    ./base/1
> 141G    ./base/16385
> 6.3M    ./base/12865
> 141G    ./base
> 12K     ./pg_multixact/offsets
> 232K    ./pg_multixact/members
> 248K    ./pg_multixact
> 348K    ./pg_xlog/archive_status
> 79G     ./pg_xlog
> 2.1M    ./global
> 4.0K    ./pg_serial
> 4.0K    ./pg_snapshots
> 4.0K    ./pg_twophase
> 12K     ./pg_notify
> 7.8M    ./pg_clog
> 288K    ./pg_stat_tmp
> 4.0K    ./pg_tblspc
> 68K     ./pg_subtrans
> 53M     ./pg_log
> 220G    .
>
> So we have 80GB in the pg_xlog on the secondary ?

Does the log for the secondary show anything unusual?

Also how did you set up the secondary?

In other words how did you populate the $DATA directory?

>
> pg_xlog]$ du -h
> 364K    ./archive_status
> 8.1G    .
>
> pg_xlog]$ du -h
> 348K    ./archive_status
> 79G     .
>
>

>
> --
>
> *Andy Erskine*
>



--
Adrian Klaver
adrian.klaver@aklaver.com

On 07/01/2015 05:20 PM, Andy Erskine wrote:
> I initially populated the directory using basebackup and pulled the data
> directory across from the master.

So what does the standby log show?


>
> On 2 July 2015 at 10:13, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 07/01/2015 05:04 PM, Andy Erskine wrote:
>
>         the cmd you listed did work it didn't like the -d option
>
>         however on the master :
>
>         data]$ du -h
>         7.9M    ./pg_clog
>         168K    ./pg_subtrans
>         232K    ./pg_multixact/members
>         120K    ./pg_multixact/offsets
>         356K    ./pg_multixact
>         12K     ./pg_notify
>         1.9G    ./pg_log
>         127G    ./base/16385
>         6.3M    ./base/12865
>         4.0K    ./base/pgsql_tmp
>         6.3M    ./base/1
>         6.4M    ./base/12870
>         127G    ./base
>         764K    ./pg_stat_tmp
>         4.0K    ./pg_tblspc
>         364K    ./pg_xlog/archive_status
>         8.1G    ./pg_xlog
>         2.2M    ./global
>         4.0K    ./pg_serial
>         4.0K    ./pg_snapshots
>         4.0K    ./pg_twophase
>         137G    .
>
>         And the secondary :
>
>         data]$ du -h
>         6.4M    ./base/12870
>         4.0K    ./base/pgsql_tmp
>         6.3M    ./base/1
>         141G    ./base/16385
>         6.3M    ./base/12865
>         141G    ./base
>         12K     ./pg_multixact/offsets
>         232K    ./pg_multixact/members
>         248K    ./pg_multixact
>         348K    ./pg_xlog/archive_status
>         79G     ./pg_xlog
>         2.1M    ./global
>         4.0K    ./pg_serial
>         4.0K    ./pg_snapshots
>         4.0K    ./pg_twophase
>         12K     ./pg_notify
>         7.8M    ./pg_clog
>         288K    ./pg_stat_tmp
>         4.0K    ./pg_tblspc
>         68K     ./pg_subtrans
>         53M     ./pg_log
>         220G    .
>
>         So we have 80GB in the pg_xlog on the secondary ?
>
>
>     Does the log for the secondary show anything unusual?
>
>     Also how did you set up the secondary?
>
>     In other words how did you populate the $DATA directory?
>
>
>
>         pg_xlog]$ du -h
>         364K    ./archive_status
>         8.1G    .
>
>         pg_xlog]$ du -h
>         348K    ./archive_status
>         79G     .
>
>
>
>
>         --
>
>         *Andy Erskine*
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
>
> *Andy Erskine*
>
> **
>
> *JDS Australia**
> **P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578
>
> *E:*_andy.erskine@jds.net.au <mailto:andy.erskine@jds.net.au>_
>
> *W*: www.jds.net.au <http://www.jds.net.au/>
> Level 8, 2 Russell Street, Melbourne, VIC 3000
> GPO Box 4777, Melbourne VIC 3001
>
> JDS Signature v1
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 07/01/2015 05:51 PM, Andy Erskine wrote:
> If i grep for errors i get some invalid page header in block 56072 of
> relation base/16385/77373 errors.
>
> But not sure what else to look for ?

Well the above seems to be the smoking gun. You have a corrupted $DATA
directory in the standby and the WAL logs being streaming from the
master are stacking up on the standby because it can not process them.
You need to shut down the standby and start over on setting up its $DATA
directory.

Just to clarify. From your previous post:

"I initially populated the directory using basebackup and pulled the
data directory across from the master."

So did you do a basebackup and then copy the data data directory over,
or where you saying the basebackup is how you pulled over the data
directory?


>

>
>
> --
>
> *Andy Erskine*
>
> **
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Hi,

So the basebackup pulls the data directory over from the master.

I am pulling over anther copy of the data directory using basebackup