Re: [ADMIN] pg_upgrade (9.1 to 9.2) bombing on casts

2013-07-15 Thread Bruce Momjian 
On Sun, Jul 14, 2013 at 10:42:22PM -0700, Sergey Konoplev wrote:
> On Thu, Jul 11, 2013 at 9:55 PM, Wells Oliver  wrote:
> > Command executed:
> >
> > time \
> > /usr/lib/postgresql/9.2/bin/pg_upgrade -k \
> > -b /usr/lib/postgresql/9.1/bin \
> > -B /usr/lib/postgresql/9.2/bin \
> > -d /data/postgresql/9.1/test \
> > -D /data/postgresql/9.2/main \
> > -o '-D /etc/postgresql/9.1/test' \
> > -O '-D /etc/postgresql/9.2/main'
> 
> In future I suggest you to use --check (from docs: check clusters
> only, don't change any data) when performing pg_upgrade first. I will
> show you all the errors but will not affect your cluster. And you can
> use safe it on a running instance BTW.

While that is a good suggstion, it would not have found this error
becaues --check does not test the restore of the database schemas.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] restoring a table after an online backup

2013-07-15 Thread Bert 
Hello,

I was just wondering: Is it possible to restore a specific table from an
online backup?
Or is it only possible if we first restore the backup, replay all the logs,
and then take the table files?

wkr,

Bert Desmet

[ADMIN] 9.2.2 - semop hanging

2013-07-15 Thread Rafael Domiciano 
Hello all you guys,

I've sent the same problem in performance list. Some answered me, but
didn't resolved the situation.

Since 2 weeks I'm get stucked in a very strange situation: from time to
time (sometimes with intervals less than 10 minutes), the server get
"stucked"/"hang" (I dont know how to call it) and every connections on
postgres (dont matter if it's SELECT, UPDATE, DELETE, INSERT, startup,
authentication...) seems like get "paused"; after some seconds (say ~10 or
~15 sec, sometimes less) everything "goes OK".

So, my first trial was to check disks. Running "iostat" apparently showed
that disks was OK. It's a Raid10, 4 600GB SAS, IBM Storage DS3512, over FC.
IBM DS Storage Manager says that disks is OK.

Then, memory. Apparently no swap being used:
[###@### data]# free -m
 total   used   free sharedbuffers cached
Mem:145182 130977  14204  0 43 121407
-/+ buffers/cache:   9526 135655
Swap: 6143 65   6078

No error on /var/log/messages.

Following is what I've tried:
1) Emre Hasegeli has suggested to reduce my shared buffers, but it's
already low:
  total server memory: 141 GB
  shared_buffers: 16 GB

Maybe it's too low? I've been thinking to increase to 32 GB.

max_connections = 500 and ~400 connections average

2) Being "hanging" on "semop" I tried the following, as suggested on some
"tuning page" over web. Is it right?

echo "250 32000 200 128" > /proc/sys/kernel/sem

3) I think my problem could be something related to "LwLocks", as I did
some googling and found some related problems and slides. There is some way
I can confirm this?

4) Rebooting the server didn't make any difference.

Following, is some strace of one process, and some others, maybe, useful
infos. Every processes I've straced bring the same scenario: seems it get
stucked on semop.

Any help appreciate,

[###@### ~]# strace -ttp 5209
Process 5209 attached - interrupt to quit
09:01:54.122445 semop(2293765, {{15, -1, 0}}, 1) = 0
09:01:55.368785 semop(2293765, {{15, -1, 0}}, 1) = 0
09:01:55.368902 semop(2523148, {{11, 1, 0}}, 1) = 0
09:01:55.368978 semop(2293765, {{15, -1, 0}}, 1) = 0
09:01:55.369861 semop(2293765, {{15, -1, 0}}, 1) = 0
09:01:55.370648 semop(3047452, {{6, 1, 0}}, 1) = 0
09:01:55.370694 semop(2293765, {{15, -1, 0}}, 1) = 0
09:01:55.370762 semop(2785300, {{12, 1, 0}}, 1) = 0
09:01:55.370805 access("base/2048098929", F_OK) = 0
09:01:55.370953 open("base/2048098929/PG_VERSION", O_RDONLY) = 5

[###@### ~]# strace -p 16877 -tt
Process 16877 attached - interrupt to quit
09:57:56.305123 semop(163844, {{13, -1, 0}}, 1) = 0
09:57:59.453714 semop(163844, {{13, -1, 0}}, 1) = 0
09:58:04.004023 semop(163844, {{13, -1, 0}}, 1) = 0
09:58:04.004209 brk(0x1f44000)  = 0x1f44000
09:58:04.004305 brk(0x1f42000)  = 0x1f42000

[###@### data]# ipcs -l

- Shared Memory Limits -
max number of segments = 4096
max seg size (kbytes) = 83886080
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1

-- Semaphore Limits 
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 200
semaphore max value = 32767

-- Messages: Limits 
max queues system wide = 32768
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536

[###@### data]# ipcs -u
- Semaphore Status ---
used arrays: 34
allocated semaphores: 546

[###@### data]# uname -a
Linux ### 2.6.32-279.14.1.el6.x86_64 #1 SMP Tue Nov 6 23:43:09 UTC 2012
x86_64 x86_64 x86_64 GNU/Linux

postgres=# select version();
   version
--
 PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6
20120305 (Red Hat 4.4.6-4), 64-bit
(1 registro)

[###@### data]# cat /etc/redhat-release
CentOS release 6.3 (Final)

Re: [ADMIN] restoring a table after an online backup

2013-07-15 Thread Gilberto Castillo 


> Hello,
>
> I was just wondering: Is it possible to restore a specific table from an
> online backup?
> Or is it only possible if we first restore the backup, replay all the
> logs,
> and then take the table files?
>

It depends on how you do the copy. To do comados with SQL, you can find to
with the sql of the table you want.

Saludos,
Gilberto Castillo
La Habana, Cuba
--- 
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at 
host imx3.etecsa.cu
Visit our web-site: , 

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] restoring a table after an online backup

2013-07-15 Thread Prashanth Ranjalkar 
>
>  > Hello,
> >
> > I was just wondering: Is it possible to restore a specific table from an
> > online backup?
> > Or is it only possible if we first restore the backup, replay all the
> > logs,
> > and then take the table files?
> >
>
> It depends on how you do the copy. To do comados with SQL, you can find to
> with the sql of the table you want.
>
> Saludos,
> Gilberto Castillo
> La Habana, Cuba



The online backups are file system level backups therefore single table
restoration is not possible however possible to restore the specific table
from the logical backups (pg_dump) if you have. In case of online backups,
it's best practice to restore the online backup on a test system and
perform PITR. After successful restoration, take the dump of the specific
table and restore it on the production.

*Thanks & Regards,*
*** *
*Prashanth Ranjalkar*
*Database Consultant & Architect*
*Email:prashant.ranjal...@gmail.com*
*Skype:prashanth.ranjalkar*
*www.postgresdba.net*


On Mon, Jul 15, 2013 at 6:49 PM, Gilberto Castillo <
> gilberto.casti...@etecsa.cu> wrote:
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>

Re: [ADMIN] 9.2.2 - semop hanging

2013-07-15 Thread Eduardo Morras 
On Mon, 15 Jul 2013 10:16:19 -0300
Rafael Domiciano  wrote:

I'm not a Linux expert, I'm a BSD man, but 

a) do you have an interrupt storm?
b) what does postgres do before the hang?
c) do you have anyother software running? Including contrib modules. It may be 
a dns lookup timeout (watch port 53 tcp/udp)
d) what filesystem? Needs filesystem some kind of "maintenance window"? I mean 
flush dirty caches, metadata, wake up barriers...


---   ---
Eduardo Morras 


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] 9.2.2 - semop hanging

2013-07-15 Thread Prashanth Ranjalkar 
On Mon, Jul 15, 2013 at 7:14 PM, Eduardo Morras  wrote:

> On Mon, 15 Jul 2013 10:16:19 -0300
> Rafael Domiciano  wrote:
>
> I'm not a Linux expert, I'm a BSD man, but
>
> a) do you have an interrupt storm?
> b) what does postgres do before the hang?
> c) do you have anyother software running? Including contrib modules. It
> may be a dns lookup timeout (watch port 53 tcp/udp)
> d) what filesystem? Needs filesystem some kind of "maintenance window"? I
> mean flush dirty caches, metadata, wake up barriers...
> 
> 

Eduardo Morras 
>

> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

>
>  ---   ---
>


max seg size (kbytes) = 83886080
max total shared memory (kbytes) = 17179869184

At initial glance, I suspect adjusting these values may resolve the issue
as SHMALL should be set either total max memory size or in number of pages.
I think the value set is too large therefore recommend comment this SHMALL
parameter and set only SHMMAX value with 24GB.

Request to verify the server logs if there are any share memory errors and
assume that work_mem value is not set to extreme higher value as the max
connections has set to 500.


*Thanks & Regards,*
*** **Prashanth Ranjalkar*
*Database Consultant & Architect*
*Email:prashant.ranjal...@gmail.com*
*Skype:prashanth.ranjalkar*
*Cell: +91 932 568 2271*
*www.postgresdba.net*

[ADMIN] iconv -c ?

2013-07-15 Thread Jan-Peter Seifert 
Hello,

 

it seems ( for quite some time now ) that PostgreSQL is using the -c option of iconv when exporting data to a different encoding:

http://www.gnu.org/savannah-checkouts/gnu/libiconv/documentation/libiconv-1.13/iconv.1.html

 

- e.g. the Euro sign is removed when exporting LATIN1 databases to UTF8.

 

I'm not sure, but in the past it was different and those characters caused error messages instead.

 

So I wonder since when incompatible characters are removed?

 

Could someone tell me, please?

 

Thank you very much!

 

Peter

 

[ADMIN] 9.2 Observer node for streaming replication

2013-07-15 Thread Jorge Torralba 
I have a master and a slave running streaming replication between the two
servers. I would like to setup some kind of third machine to play the role
of observer node to monitor the replication and do the automatic fail over.
 However, I can't find any documentation or suggestions on such an
implementation. Is it possible to do with 9.2.4 or will I need to rely on
some sort of F5 load balancer to handle the auto failover?

-- 
Thanks,

Jorge Torralba

Re: [ADMIN] 9.2 Observer node for streaming replication

2013-07-15 Thread Joshua D. Drake 


On 7/15/2013 8:55 AM, Jorge Torralba wrote:
I have a master and a slave running streaming replication between the 
two servers. I would like to setup some kind of third machine to play 
the role of observer node to monitor the replication and do the 
automatic fail over.  However, I can't find any documentation or 
suggestions on such an implementation. Is it possible to do with 9.2.4 
or will I need to rely on some sort of F5 load balancer to handle the 
auto failover?


Depends on your OS. We do this all the time with our HAC setups and we 
don't need any kind of load balancer to pull it off.


JD



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] 9.2.2 - semop hanging

2013-07-15 Thread Prashanth Ranjalkar 

On 15-07-2013 19:14, Eduardo Morras wrote:

On Mon, 15 Jul 2013 10:16:19 -0300
Rafael Domiciano  wrote:

I'm not a Linux expert, I'm a BSD man, but

a) do you have an interrupt storm?
b) what does postgres do before the hang?
c) do you have anyother software running? Including contrib modules. It may be 
a dns lookup timeout (watch port 53 tcp/udp)
d) what filesystem? Needs filesystem some kind of "maintenance window"? I mean 
flush dirty caches, metadata, wake up barriers...


---   ---
Eduardo Morras 

Max seg size, max total shared memory  represents SHMMAX and SHMALL 
respectively.

max seg size (kbytes) = 83886080
max total shared memory (kbytes) = 17179869184 

At initial glance, I suspect adjusting these values may resolve the 
issue as SHMALL should be set either total max memory size or in number 
of pages. I think the value set is too large therefore recommend comment 
this SHMALL parameter and set only SHMMAX value with 24GB.


Request to verify the server logs if there are any share memory errors 
and assume that work_mem value is not set to extreme higher value as the 
max connections has set to 500.

/*Thanks & Regards,*/
//**
/*Prashanth Ranjalkar*/
/*Database Consultant & Architect*/
/*Email:prashant.ranjal...@gmail.com* 
/

/*Skype:prashanth.ranjalkar*/
/*Cell: +91 932 568 2271*/
*/www.postgresdba.net /*

Re: [ADMIN] 9.2 Observer node for streaming replication

2013-07-15 Thread Jorge Torralba 
Joshua,

We are running centos 6.2. Could you provide some insight or point in the
direction of your methodology for such implementation?

Thanks

JT


On Mon, Jul 15, 2013 at 9:15 AM, Joshua D. Drake wrote:

>
> On 7/15/2013 8:55 AM, Jorge Torralba wrote:
>
>> I have a master and a slave running streaming replication between the two
>> servers. I would like to setup some kind of third machine to play the role
>> of observer node to monitor the replication and do the automatic fail over.
>>  However, I can't find any documentation or suggestions on such an
>> implementation. Is it possible to do with 9.2.4 or will I need to rely on
>> some sort of F5 load balancer to handle the auto failover?
>>
>
> Depends on your OS. We do this all the time with our HAC setups and we
> don't need any kind of load balancer to pull it off.
>
> JD
>
>


-- 
Thanks,

Jorge Torralba


Note: This communication may contain privileged or other confidential
information. If you are not the intended recipient, please do not print,
copy, retransmit, disseminate or otherwise use the information. Please
indicate to the sender that you have received this email in error and
delete the copy you received. Thank You.

Re: [ADMIN] pg_upgrade (9.1 to 9.2) bombing on casts

2013-07-15 Thread Sergey Konoplev 
On Mon, Jul 15, 2013 at 1:47 AM, Bruce Momjian  wrote:
>> In future I suggest you to use --check (from docs: check clusters
>> only, don't change any data) when performing pg_upgrade first. I will
>> show you all the errors but will not affect your cluster. And you can
>> use safe it on a running instance BTW.
>
> While that is a good suggstion, it would not have found this error
> becaues --check does not test the restore of the database schemas.

Sure. Thank you Bruce.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray...@gmail.com


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] Postgres upgrade from version 9.04 to 9.1.3

2013-07-15 Thread Mónica Gamarra 
Hello,
I am planning to upgrade our databases from 9.0.4 to 9.1.3
and I found a problem while upgrading one database that has
many tablespaces. The upgrade  fails with the following error

"Cannot migrate to/from the same system catalog version
when using tablespaces"


Why 9.0.4 and 9.1.3 are same catalog version?

Is there a workarround for this problem, I would like to avoid
using a dmp.


Thanks

-- 

Mónica Gamarra Barrios
monica.gama...@gmail.com

Re: [ADMIN] Postgres upgrade from version 9.04 to 9.1.3

2013-07-15 Thread Prashanth Ranjalkar 
*
*
On Mon, Jul 15, 2013 at 11:24 PM, Mónica Gamarra
wrote:

> Hello,
> I am planning to upgrade our databases from 9.0.4 to 9.1.3
> and I found a problem while upgrading one database that has
> many tablespaces. The upgrade  fails with the following error
>
> "Cannot migrate to/from the same system catalog version
> when using tablespaces"
>
>
> Why 9.0.4 and 9.1.3 are same catalog version?
>
> Is there a workarround for this problem, I would like to avoid
> using a dmp.
>
>
> You can also consider slony for performing the upgrade if needed.


*

Thanks & Regards,*
*** *
*Prashanth Ranjalkar*
*Database Consultant & Architect*
*Email:prashant.ranjal...@gmail.com*
*Skype:prashanth.ranjalkar*
*Cell: +91 932 568 2271*
*www.postgresdba.net*


> Thanks
>
> --
> 
> Mónica Gamarra Barrios
> monica.gama...@gmail.com
>

Re: [ADMIN] 9.2.2 - semop hanging

2013-07-15 Thread Kevin Grittner 
Rafael Domiciano  wrote:

> PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc
> (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

> CentOS release 6.3 (Final)

> Since 2 weeks I'm get stucked in a very strange situation: from
> time to time (sometimes with intervals less than 10 minutes), the
> server get "stucked"/"hang" (I dont know how to call it) and
> every connections on postgres (dont matter if it's SELECT,
> UPDATE, DELETE, INSERT, startup, authentication...) seems like
> get "paused"; after some seconds (say ~10 or ~15 sec, sometimes
> less) everything "goes OK".

During these episodes, do you see high system CPU time?  If so, try
disabling transparent huge page support, and see whether it affects
the frequency or severity of the episodes.

> So, my first trial was to check disks. Running "iostat"
> apparently showed that disks was OK.

Did you run iostat during an episode of slowness?  What did it
show?  Giving an interpretation that it as "apparently OK" doesn't
provide much useful information.

> It's a Raid10, 4 600GB SAS, IBM Storage DS3512, over FC. IBM DS
> Storage Manager says that disks is OK.

Are there any reports to show you when writing was saturated?

>  total   used   free shared    buffers    cached
> Mem:    145182 130977  14204  0 43    121407
> -/+ buffers/cache:   9526 135655
> Swap: 6143 65   6078

> Following is what I've tried:
> 1) Emre Hasegeli has suggested to reduce my shared buffers, but
> it's already low:
>   total server memory: 141 GB
>   shared_buffers: 16 GB

On a machine with nearly twice that RAM, I've had to decrease
shared_buffers to 2GB to avoid the symptoms you describe.  That is
in conjunction with making the background writer more aggressive
and making sure the checkpoint completion target is set to 0.9.

> Maybe it's too low? I've been thinking to increase to 32 GB.

Well, you could try that; if the symptoms get worse, then you might
be willing to go the other direction

> max_connections = 500 and ~400 connections average

How many cores (not "hardware threads") does the machine have?  You
will probably have better throughput and latency if you use
connection pooling to limit the number of active database
transactions to somewhere arount two times the number of cores, or
slightly above that.

-- 
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] Help with PITR in PostgreSQL 8.4

2013-07-15 Thread Nestor A. Diaz 
Hello People,

I need some clarification on Continuous Archiving and Point-In-Time
Recovery (PITR).

I have been able to replicate a postgresql 8.4 database from one machine
to another using rsync and wal files.

I do this as follows (Using debian gnu/linux squeeze):

In *master* i have the following options under postgresql.conf:

archive_mode = on   # allows archiving to be done
archive_command = 'test ! -f
/var/lib/postgresql/8.4/main/backup_in_progress || cp -i %p
/var/lib/postgresql/8.4/main/wal-archive/%f'   # command to use to
archive a logfile segment

I start the backup on *master*:

touch /var/lib/postgresql/8.4/main/backup_in_progress
DATE=$(date +%Y%m%d-%H%M%S)
psql  --cluster 8.4/main -c "SELECT pg_start_backup('backup_${DATE}');"

Then rsync on *standby*.

rsync -avz --partial --progress --delete --rsh=ssh \
--exclude 'lost+found' --exclude 'postmaster.pid' --exclude
'postmaster.opts' --exclude 'wal-archive' \
postgres@$SRC_HOST:/var/lib/postgresql/8.4/main/
/var/lib/postgresql/8.4/main/

Plus all the tablespaces which are located on different directories and
rsynced with same options as above (except the exclude)

Then stop backup on *main*.

psql  --cluster 8.4/main -c "SELECT pg_stop_backup();"

And transfer all the WAL files from *master* to *standby*:

rsync -avz --partial --progress --rsh=ssh \
 --exclude 'lost+found' \
 postgres@$SRC_HOST:/var/lib/postgresql/8.4/main/wal-archive/
/var/lib/postgresql/8.4/main/wal-archive/

In *standby* i have in recovery.conf:

restore_command = '/usr/lib/postgresql/8.4/bin/pg_standby -l -d -s 2 -t
/var/run/postgresql/8.4-main.trigger
/var/lib/postgresql/8.4/main/wal-archive %f %p %r
2>>/var/log/postgresql/postgresql-8.4-main-standby.log'
recovery_end_command = 'rm -f /var/run/postgresql/8.4-main.trigger'

Then start the database on *standby* and everything worked fine, I
replicated the main database from one machine to another and later on
the *standby* machine I process the logical backups which off course
takes a lot of time.

But there is one big issue:

The database is about: 300 Gb of size, so rsyncing the data from one
machine to another takes more than 2 hours, I don't know if I can change
some rsync options in order to improve since this is too much time.

Another thing is to improve the script and not having to rsync the
database and just transferring the wal files and apply them every day,
this is what I am trying to do without luck.

So the question is: Can I have some continues wal archiving and then
transfer all WAL files to *standby* everyday, recover the database and
backup all data without having to rsync the data every time i make a
backup ?

For now i have to stick with 8.4 .

Thank you very much for your time and support.

Slds.

-- 
Typed on my key64.org keyboard

Nestor A Diaz



-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] 777 permissions on basebackup directory

2013-07-15 Thread prakhar jauhari 
Hi all,
I am currently using  postgresql 9.2 installation.
While taking base backups using pg_basebackup utility, I noticed that
permissions of the directory which i specify for base backup are set to
777. Does postgesql need such lenient permissions to work, coz 777 on Db
directory seems a security concern to me.

Regards,
Prakhar.