Streaming Questions and Sizing
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
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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 = 500That 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: CADqp7UAtwKedOQJKwia0tFAt7QnoktOJq-TYxG0dFObvX=-=uA@mail.gmail.com
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 ?
pg_xlog]$ du -h
364K ./archive_status
8.1G .
pg_xlog]$ du -h
348K ./archive_status
79G .
On 1 July 2015 at 23:59, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 07/01/2015 06:56 AM, Andy Erskine wrote:
/opt/database on both servers has been assigned 250gb only postgres has
been installed on them.I cannot get the output you requested til back at work.
When you do, post that information to the list.
Cheers.
--
Adrian Klaver
adrian.klaver@aklaver.com
--
*Andy Erskine*
*JDS Australia*
*P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578
*E:* *andy.erskine@jds.net.au <andy.erskine@jds.net.au>*
*W*: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001
[image: JDS Signature v1]
Attachments:
Import Notes
Reply to msg id not found: 5593F237.1000101@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I initially populated the directory using basebackup and pulled the data
directory across from the master.
On 2 July 2015 at 10:13, Adrian Klaver <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
--
*Andy Erskine*
*JDS Australia*
*P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578
*E:* *andy.erskine@jds.net.au <andy.erskine@jds.net.au>*
*W*: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001
[image: JDS Signature v1]
Attachments:
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 3001JDS Signature v1
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 ?
On 2 July 2015 at 10:41, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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 3001JDS Signature v1
--
Adrian Klaver
adrian.klaver@aklaver.com
--
*Andy Erskine*
*JDS Australia*
*P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578
*E:* *andy.erskine@jds.net.au <andy.erskine@jds.net.au>*
*W*: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001
[image: JDS Signature v1]
Attachments:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi,
So the basebackup pulls the data directory over from the master.
I am pulling over anther copy of the data directory using basebackup
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