[GENERAL] help for this situation

[GMAIL]

i describe the situation:
i have two pc with postgressql server:
- a main pc, with ip 192.168.5.1 turned on
- a "backup" pc, with ip 192.168.5.1 turned off

i want that the main pc saves the database in local hard drive and on a 
nas real-time. when the main pc has a failure, i turn on, manually, the 
secondary pc, that reads the database from the nas


how i can do that?


--
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] Streaming replication failed to start scenarios

[Albe Laurenz]

chinnaobi wrote:
> I have been testing streaming replication in windows with postgres
9.1.1.
> For few scenario's I haven't found a solution. Please advice me.
> 
> 1. Precautions before promoting standby server to primary manually
> considering the dead primary server ??

You don't have to take any precautions; the standby can be promoted
as soon as "consistent recovery state reached" appears in the log.

> 2. How could we ensure the standby has received all transactions sent
by
> primary till the point primary server is dead. (Meaning the dead
primary and
> standby server are exactly same, so that the dead primary comes back
it can
> be turned to standby without any issues).

If the primary is truy dead, there is no way (unless you use synchronous
replication, then it is always the case).

You can use pg_last_xlog_receive_location() on the standby to see the
last
replayed transaction ID and pg_last_xact_replay_timestamp() for
the timestamp.
If the primary is still there, you can use txid_current() to get the
current transaction ID.

> 3. When the dead primary is switchedto standby the streaming is not
> happening due to current_wal_location is ahead in the standby server
is
> ahead of wal_sent_location. In this case how can I start streaming
without
> taking a fresh base backup from current primary ??

The parenthesis in your previous question and this question suggest
that you missed out on one thing:

When the standby is promoted, it starts a new time line, so it
is on a different time line from the old master.  The old
primary cannot be turned to a standby without a new base backup
(possibly via rsync to speed up things).

This time line switch is a mechanism that prevents precisely
the problems you are anticipating.

> 4. When the dead primary comes back the DB still accepts data and it
goes to
> out of sync with the current primary and streaming won't start. Is
there any
> solution for this case ??

You should lock out connections to the old primary, perhaps
via pg_hba.conf.

Yours,
Laurenz Albe


-- 
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] Replication - doubts

[Albe Laurenz]

Jayadevan M wrote:
> I went through the documentation on replication. and am trying to
understand the various options
> available.
> 
> Does replication available in PostgreSQL always use WAL (i.e
irrespective of whether it is
> synchronous/asynchronous, whether it is using archived logs or 'live'
logs or streaming)?

Yes as far as replication solutions included in core are concerned.

There are other replication solutions (like Slony) using triggers.

> I understand that we can use WALs in 3 modes
> 
> 1)  Use the archived WALs - results in the slave being
significantly behind the master. This is
> set up using the archive_command
> 
> 2)  Use entire WAL segments

I am not sure what the difference between 1) and 2) is.
There will be a significant lag, yes.

> 3)  Use 'streaming' , i.e. use WAL files before they are complete
- in this case the slave is
> almost uptodate

Right.

> If I set up a slave using the archived WAls and set hot_standby on in
the slave, will I be able to
> read from the slave?

Yes.

Yours,
Laurenz Albe


-- 
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] help for this situation

[Albe Laurenz]

GMAIL wrote:
> i describe the situation:
> i have two pc with postgressql server:
> - a main pc, with ip 192.168.5.1 turned on
> - a "backup" pc, with ip 192.168.5.1 turned off
> 
> i want that the main pc saves the database in local hard drive and on
a
> nas real-time. when the main pc has a failure, i turn on, manually,
the
> secondary pc, that reads the database from the nas
> 
> how i can do that?

What do you want to guard against? Hardware outage?
Software bugs?

I don't think that the scenario you describe is possible.

There are other, maybe better ways:

1) Have the database on NAS and use storage mirroring.
   NAS in that case means NFS (hard mount!), *not CIFS*.
   That will help against hardware outage, but not against
   filesystem or database corruption.

2) Use a standby database with streaming replication.
   That will also help with filesystem and database
   corruption.

Yours,
Laurenz Albe


-- 
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] 9.1/9.2 SERIALIZABLE: expected serialization failure between INSERT and SELECT not encountered

[Albe Laurenz]

Craig Ringer wrote:
> In this SO question:
> 
>
http://dba.stackexchange.com/questions/26905/how-do-i-implement-insert-i
f-not-found-for-transactions-
> at-serializable-isolatio/26909#26909
> 
> the author is running a series of queries that I'd expect to abort on
commit with a serialisation
> failure. No such failure occurs, and I'm wondering why.
> 
> SETUP
> 
> create table artist (id serial primary key, name text);
> 
> 
> 
> SESSION 1   SESSION 2
> 
> BEGIN ISOLATION LEVEL SERIALIZABLE;
> 
> BEGIN ISOLATION LEVEL
SERIALIZABLE;
> 
> SELECT id FROM artist
> WHERE name = 'Bob';
> 
> 
> INSERT INTO artist (name)
> VALUES ('Bob')
> 
> INSERT INTO artist (name)
> VALUES ('Bob')
> 
> 
> COMMIT; COMMIT;
> 
> 
> I'd expect one of these two to abort with a serialization failure and
I'm not sure I understand why
> they don't in 9.1/9.2's new serializable mode. Shouldn't the SELECT
for "Bob" cause the insertion of
> "Bob" in the other transaction to violate serializability?

Why? They can be serialized. The outcome would be exactly the same
if session 2 completed before session 1 began.

You would have a serialization problem if each session tried
to read what the other tries to write:

SESSION 1   SESSION 2

BEGIN ISOLATION LEVEL SERIALIZABLE;

BEGIN ISOLATION LEVEL SERIALIZABLE;

INSERT INTO artist (name) VALUES ('Bob');

INSERT INTO artist (name) VALUES
('Bob');

SELECT * FROM artist WHERE name = 'Bob';

SELECT * FROM artist WHERE name =
'Bob';

COMMIT;

COMMIT; /* throws serialization
error */

Yours,
Laurenz Albe


-- 
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] Streaming replication failed to start scenarios

[chinnaobi]

Hey Laurenz Albe,

Thank you for the quick reply..

> 3. When the dead primary is switchedto standby the streaming is not 
> happening due to current_wal_location is ahead in the standby server 
is 
> ahead of wal_sent_location. In this case how can I start streaming 
without 
> taking a fresh base backup from current primary ?? 

>>The parenthesis in your previous question and this question suggest 
>>that you missed out on one thing: 

>>When the standby is promoted, it starts a new time line, so it 
>>is on a different time line from the old master.  The old 
>>primary cannot be turned to a standby without a new base backup 
>>(possibly via rsync to speed up things). 

>>This time line switch is a mechanism that prevents precisely 
>>the problems you are anticipating. 

But taking 200 GB to 500 GB DB base backup is impossible for me. Is there
any other solution ??





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Streaming-replication-failed-to-start-scenarios-tp5728519p5728557.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Streaming replication failed to start scenarios

[Albe Laurenz]

chinnaobi wrote:
>> When the standby is promoted, it starts a new time line, so it
>> is on a different time line from the old master.  The old
>> primary cannot be turned to a standby without a new base backup
>> (possibly via rsync to speed up things).

> But taking 200 GB to 500 GB DB base backup is impossible for me. Is
there
> any other solution ??

But you must take backups anyway, right?

Using rsync should make the backup much faster if nothing
much has happened since failover.

Yours,
Laurenz Albe


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

[GENERAL] PostgreSQL Magazine

[Fathi Ben Nasr]

Hello,

Is there a PostgreSQL magazine like the one sent by Oracle to whom
requests it ?

If answer is yes: how to sbscribe to it ?

Else

I know this could cost a lot to print such magazines, but maybe a pdf
version could be affordable.

The main idea behind this is to "show" there a big active community
behind PostgreSQL, commercial derivatives and success stories of
companies, not necesserly fortune 500, using PostgreSQL or products,
like OpenERP, that use it as their backend db server.

Other contents, like function/script of the month, tutorials, etc could
enrich the content of the magazine.
End;




-- 
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] PostgreSQL Magazine

[Albe Laurenz]

Fathi Ben Nasr wrote:
> Is there a PostgreSQL magazine like the one sent by Oracle to whom
> requests it ?
> 
> If answer is yes: how to sbscribe to it ?
> 
> Else
> 
> I know this could cost a lot to print such magazines, but maybe a pdf
> version could be affordable.
> 
> The main idea behind this is to "show" there a big active community
> behind PostgreSQL, commercial derivatives and success stories of
> companies, not necesserly fortune 500, using PostgreSQL or products,
> like OpenERP, that use it as their backend db server.
> 
> Other contents, like function/script of the month, tutorials, etc could
> enrich the content of the magazine.
> End;

There's PG Mag: http://pgmag.org/Start

It does not appear regularly (yet?).

Yours,
Laurenz Albe

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

[GENERAL] pgBadger 2.1 released

[damien clochard]

Paris, France - October 17th, 2012

DALIBO is proud to announce the release of version 2.1 of pgBadger, the
new PostgreSQL log analyzer. pgBadger is built for speed with fully
detailed reports from your PostgreSQL log file. It's a single and small
Perl script that aims to replace and to outperform the old PHP script
pgFouine.

= pgBadger 2.1 allows more flexibility =

pgBadger is back with a new release and some long-awaited features:

  * you can now use a custom log_line_prefix (see below)
  * you can provide log files compressed with gzip, bzip2 or zip tools
  * you can use log_duration instead of log_min_duration_statement

This new release comes with some bugfixes and many improvements such as :

  * Huge overhaul of the cvslog format parsing for better performance
  * New syslog-ng parser
  * Better handling of empty graphs and tables
  * Handling of autovacuum launcher messages
  * Report of configuration changes

For the complete list of changes, please checkout the release note on
https://github.com/dalibo/pgbadger/blob/master/ChangeLog

All pgBadger users should upgrade as soon as possible.


= How to use the new --prefix parameter ? =

With pgBadger 2.1, you don't need to modify the log_line_prefix
parameter in the postgresql.conf file. Instead you can use the --prefix
option to describe your log_line_prefix to pgBadger.

For instance, you can run the following command line:

perl pgbadger --prefix '%m %u@%d %p %r %a : ' postgresql.log

However, if you are using the stderr output, you will need to log at
least the timestamp (%t), the PID (%p) and the session/line number (%l).


= Links & Credits =

DALIBO would like to thank the developers who submitted patches and the
users who reported bugs and feature requests, especially Philip Freeman,
Casey Allen Shobe and Vincent Laborie. pgBadger is an open project. Any
contribution to build a better tool is welcome. You just have to send
your ideas, features requests or patches using the GitHub tools or
directly on our mailing list.

Links :

  * Download :  https://github.com/dalibo/pgbadger/downloads
  * Mailing List :
https://listes.dalibo.com/cgi-bin/mailman/listinfo/pgbagder




--

**About pgBadger** :

pgBagder is a new generation log analyzer for PostgreSQL, created by
Gilles Darold, also author of ora2pg migration tool. pgBadger is a fast
and easy tool to analyze your SQL traffic and create HTML5 reports with
dynamics graphs. pgBadger is the perfect tool to understand the behavior
of your PostgreSQL server and identify which SQL queries need to be
optimized.

Docs, Download & Demo at http://dalibo.github.com/pgbadger/

--

**About DALIBO** :

DALIBO is the leading PostgreSQL company in France, providing support,
trainings and consulting to its customers since 2005. The company
contributes to the PostgreSQL community in various ways, including :
code, articles, translations, free conferences and workshops

Check out DALIBO's open source projects at http://dalibo.github.com


-- 
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] PostgreSQL Magazine

[damien clochard]

Le 17/10/2012 12:35, Fathi Ben Nasr a écrit :
> Hello,
>

Hi

> Is there a PostgreSQL magazine like the one sent by Oracle to whom
> requests it ?
> 

PostgreSQL Magazine started two years ago. The second issue will be
released in a few weeks.

So far the magazine is available for free online (download the PDF or
read it with the flash reader). You can also buy your own paper version
(http://pgmag.org/01/buy). You can also get one for free in some
PostgreSQL conferences thanks to our sponsors.

> If answer is yes: how to sbscribe to it ?
> 

You can subscribe to our newletter and twitter account to be informed
when a new issue is released

https://twitter.com/intent/follow?&screen_name=pg_mag
http://pgmag.us2.list-manage.com/subscribe?u=d23db8f49246eb6e74c6ca21a&id=f1bf0dbe7d

> I know this could cost a lot to print such magazines, but maybe a pdf
> version could be affordable.
> 
> The main idea behind this is to "show" there a big active community
> behind PostgreSQL, commercial derivatives and success stories of
> companies, not necesserly fortune 500, using PostgreSQL or products,
> like OpenERP, that use it as their backend db server.
> 
> Other contents, like function/script of the month, tutorials, etc could
> enrich the content of the magazine.

This is what we intend to do ! Please note that magazine itself works
like an open source project. You can join us and participate ! There are
many ways to get involved : writing articles, editing, proof-reading,
layout design, translating, etc.

If you want to contribute, you can join the project mailing list :
https://groups.google.com/group/pgmag/

Regards,

--
damien



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

[GENERAL] allow servers to access to the same data

[GMAIL]

it's possible to access the same data from two different servers. the 
two servers have the same IP and not run simultaneously



--
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] allow servers to access to the same data

[Craig Ringer]

On 17/10/2012 9:20 PM, GMAIL wrote:
it's possible to access the same data from two different servers. the 
two servers have the same IP and not run simultaneously


On shared storage? Yes, but it's a bad idea, because if they're ever 
both started at the same time the data will be critically corrupted. You 
need utterly reliable STONITH, preferably power-cut style.


It sounds like you're attempting a shared storage fail-over system. 
That's ... not ideal. You're better off with replication based failover. 
You should probably explain what you're trying to do and why, so better 
advice can be offered.



--
Craig Ringer


--
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] allow servers to access to the same data

[Tulio]

  
  
You can use a stream replication in hot standby (native) to have the
same data and access in both (but not update and insert in the
slave, just select) 
and create a virtual IP using heartbeat.. configuring a master to
use some IP (virtual) and when this lost the IP, the second server
(slave) will be use this IP..
and will allow update and insert...

is it that you want?


Em 17/10/2012 10:20, GMAIL escreveu:
it's
  possible to access the same data from two different servers. the
  two servers have the same IP and not run simultaneously
  
  
  

  

Re: [GENERAL] PostgreSQL Magazine

[Fathi Ben Nasr]

Thank You.


On Wed, 2012-10-17 at 14:44 +0200, damien clochard wrote:
> Le 17/10/2012 12:35, Fathi Ben Nasr a écrit :
> > Hello,
> >
> 
> Hi
> 
> > Is there a PostgreSQL magazine like the one sent by Oracle to whom
> > requests it ?
> > 
> 
> PostgreSQL Magazine started two years ago. The second issue will be
> released in a few weeks.
> 
> So far the magazine is available for free online (download the PDF or
> read it with the flash reader). You can also buy your own paper version
> (http://pgmag.org/01/buy). You can also get one for free in some
> PostgreSQL conferences thanks to our sponsors.
> 
> > If answer is yes: how to sbscribe to it ?
> > 
> 
> You can subscribe to our newletter and twitter account to be informed
> when a new issue is released
> 
> https://twitter.com/intent/follow?&screen_name=pg_mag
> http://pgmag.us2.list-manage.com/subscribe?u=d23db8f49246eb6e74c6ca21a&id=f1bf0dbe7d
> 
> > I know this could cost a lot to print such magazines, but maybe a pdf
> > version could be affordable.
> > 
> > The main idea behind this is to "show" there a big active community
> > behind PostgreSQL, commercial derivatives and success stories of
> > companies, not necesserly fortune 500, using PostgreSQL or products,
> > like OpenERP, that use it as their backend db server.
> > 
> > Other contents, like function/script of the month, tutorials, etc could
> > enrich the content of the magazine.
> 
> This is what we intend to do ! Please note that magazine itself works
> like an open source project. You can join us and participate ! There are
> many ways to get involved : writing articles, editing, proof-reading,
> layout design, translating, etc.
> 
> If you want to contribute, you can join the project mailing list :
> https://groups.google.com/group/pgmag/
> 
> Regards,
> 
> --
> damien
> 
> 
> 





-- 
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] Strategies/Best Practises Handling Large Tables

[Chris Travers]

On Fri, Oct 12, 2012 at 7:44 AM, Chitra Creta  wrote:

> Hi,
>
> I currently have a table that is growing very quickly - i.e 7 million
> records in 5 days. This table acts as a placeholder for statistics, and
> hence the records are merely inserted and never updated or deleted.
>
> Many queries are run on this table to obtain trend analysis. However,
> these queries are now starting to take a very long time (hours) to execute
> due to the size of the table.
>
> I have put indexes on this table, to no significant benefit.  Some of the
> other strategies I have thought of:
> 1. Purge old data
> 2. Reindex
> 3. Partition
> 4. Creation of daily, monthly, yearly summary tables that contains
> aggregated data specific to the statistics required
>
> Does anyone know what is the best practice to handle this situation?
>

The answer is well, it depends.  Possibly some combination.

One approach I like that may be included in #4 but not necessarily is the
idea of summary tables which contain snapshots of the data, allowing you to
roll forward or backward from defined points.  This is what I call the log,
aggregate, and snapshot approach.   But it really depends on what you are
doing and there is no one size fits all approach at this volume.

Instead of reindexing, I would suggest also looking into partial indexes.

Best Wishes,

Re: [GENERAL] PostgreSQL training recommendations?

[Vincent Veyron]

I am surprised none of the fine contributors to this thread mentionned
an activity they practice extensively, which is reading this list's
content every day.

Best training material ever in my opinion.

-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des assurances sinistres et des dossiers contentieux pour 
le service juridique



-- 
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] PostgreSQL Magazine

[Fathi Ben Nasr]

Thank You.


On Wed, 2012-10-17 at 12:49 +0200, Albe Laurenz wrote:
> Fathi Ben Nasr wrote:
> > Is there a PostgreSQL magazine like the one sent by Oracle to whom
> > requests it ?
> > 
> > If answer is yes: how to sbscribe to it ?
> > 
> > Else
> > 
> > I know this could cost a lot to print such magazines, but maybe a pdf
> > version could be affordable.
> > 
> > The main idea behind this is to "show" there a big active community
> > behind PostgreSQL, commercial derivatives and success stories of
> > companies, not necesserly fortune 500, using PostgreSQL or products,
> > like OpenERP, that use it as their backend db server.
> > 
> > Other contents, like function/script of the month, tutorials, etc could
> > enrich the content of the magazine.
> > End;
> 
> There's PG Mag: http://pgmag.org/Start
> 
> It does not appear regularly (yet?).
> 
> Yours,
> Laurenz Albe





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

[GENERAL] to_date() accepts wrong input data

[Andreas Kretschmer]

Hi,

I'm a little bit astonished:

test=*# select to_date('2012/30/03','/mm/dd');
  to_date

 2014-06-07
(1 row)

test=*# select to_date('2013/02/29','/mm/dd');
  to_date

 2013-03-01
(1 row)


Bug or feature? Version is 9.2.0



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
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] to_date() accepts wrong input data

[Tom Lane]

Andreas Kretschmer  writes:
> Hi,
> I'm a little bit astonished:

> test=*# select to_date('2012/30/03','/mm/dd');
>   to_date
> 
>  2014-06-07
> (1 row)

> test=*# select to_date('2013/02/29','/mm/dd');
>   to_date
> 
>  2013-03-01
> (1 row)

> Bug or feature? Version is 9.2.0

Feature, I'm afraid --- people are used to that behavior, see eg
http://archives.postgresql.org/pgsql-general/2012-10/msg00209.php

If you want tighter checking, don't use to_date, use the regular
datetime input functions (eg, via a cast to date or timestamp).

regards, tom lane


-- 
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] PostgreSQL training recommendations?

[Chris Angelico]

On Thu, Oct 18, 2012 at 12:56 AM, Vincent Veyron  wrote:
>
> I am surprised none of the fine contributors to this thread mentionned
> an activity they practice extensively, which is reading this list's
> content every day.
>
> Best training material ever in my opinion.

A pay-for magazine you can probably claim on your taxes as a necessary
expense. Is it possible somehow to claim that reading this list is
vital to your work, and therefore the 5 hours a week you spend
answering other threads (in order to repay the community) is a
legitimate work expense? :)

ChrisA


-- 
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] How to avoid base backup in automated failover

[chinnaobi [via PostgreSQL]]

Hey Amitkapila,

Thank you for the quick reply.

How can implement this patch in windows, because I am using windows 9.1.1
postgreSQL application ?? 



___
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-avoid-base-backup-in-automated-failover-tp5711147p5728562.html

To unsubscribe from How to avoid base backup in automated failover, visit 
http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5711147&code=cGdzcWwtZ2VuZXJhbEBwb3N0Z3Jlc3FsLm9yZ3w1NzExMTQ3fDk5Mzg2MjUzMg==

Re: [GENERAL] How to avoid base backup in automated failover

[amitkapila [via PostgreSQL]]

On Wednesday, October 17, 2012 3:09 PM chinnaobi wrote:
> Hey Amitkapila,
> 
> Thank you for the quick reply.
> 
> How can implement this patch in windows, because I am using windows
> 9.1.1
> postgreSQL application ??
> 

If the patch serves the feature you require, then once it gets committed
(there are few bugs yet to be resolved), the feature will be available for
windows as well.
About the version, I think it will be available in 9.3 only. 
If you are very urgent need of this, may be you can merge in your own copy
of 9.1.1. 
However that has its own implications.

With Regards,
Amit Kapila.



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




___
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/How-to-avoid-base-backup-in-automated-failover-tp5711147p5728573.html

To unsubscribe from How to avoid base backup in automated failover, visit 
http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5711147&code=cGdzcWwtZ2VuZXJhbEBwb3N0Z3Jlc3FsLm9yZ3w1NzExMTQ3fDk5Mzg2MjUzMg==

[GENERAL] problem with distinct not distincting...

[John Beynon]

I have a pretty basic query;

select distinct on (name) name, length(name) from
drugs
where customer_id IS NOT NULL
order by name;

which I'd expect to only return me a single drug name if there are
duplicates, yet I get

name | length
==
Roaccutane | 10
Roaccutane | 10

table encoding is UTF8...

I'm scratching my head!

Thanks,

John.


-- 
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] PostgreSQL training recommendations?

[Scott Marlowe]

On Wed, Oct 17, 2012 at 8:42 AM, Chris Angelico  wrote:
> On Thu, Oct 18, 2012 at 12:56 AM, Vincent Veyron  wrote:
>>
>> I am surprised none of the fine contributors to this thread mentionned
>> an activity they practice extensively, which is reading this list's
>> content every day.
>>
>> Best training material ever in my opinion.
>
> A pay-for magazine you can probably claim on your taxes as a necessary
> expense. Is it possible somehow to claim that reading this list is
> vital to your work, and therefore the 5 hours a week you spend
> answering other threads (in order to repay the community) is a
> legitimate work expense? :)

I've been on more than one job interview where the guy interviewing me
is someone who's question I've answered in the past here.


-- 
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] Does Postgres Object-Relational Syntax follow Standard?

[Will Rutherdale (rutherw)]

> -Original Message-
> From: Craig Ringer [mailto:ring...@ringerc.id.au]
> Sent: 16 October 2012 21:27
> To: Will Rutherdale (rutherw)
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Does Postgres Object-Relational Syntax follow
> Standard?
> 
> On 10/17/2012 05:00 AM, Will Rutherdale (rutherw) wrote:
> > Hi.
> >
> > I was having a discussion with people at work about the Postgres object-
> relational syntax.
> 
> What syntax specifically? Do you mean table inheritance and SELECT ONLY ?

Just the basic Postgres Object notation, such as CREATE TABLE ... INHERITS, 
along with related syntax one might use with it.  The ONLY keyword would be 
part of that.

People looking at Postgres might reasonably ask, what does its Object support 
do, and does it follow a standard.

-Will



-- 
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] Does Postgres Object-Relational Syntax follow Standard?

[Will Rutherdale (rutherw)]

From: Chris Travers [mailto:chris.trav...@gmail.com] 
Sent: 16 October 2012 22:37
To: Will Rutherdale (rutherw)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Does Postgres Object-Relational Syntax follow Standard?


On Tue, Oct 16, 2012 at 2:00 PM, Will Rutherdale (rutherw)  
wrote:
Hi.


Not really.  The one area the standard discusses in this area, namely single 
inheritance for structured data types, is not supported by PostgreSQL. As far 
as I can tell, the SQL 2003 standard followed more or less the approach 
Illustra (which began as  a Pg fork but had a totally independent SQL 
implementation) but limited it to structured data types only and thus avoided 
issues like jagged rows (which survive in Informix, but pose practical 
programming challenges and therefore have never been supported on PostgreSQL).  
The one area that is supported is CREATE TABLE foo OF TYPE bar; but that's 
pretty anemic support if you ask me.

In my view, while there are rough edges, the PostgreSQL approach is richer than 
the SQL 2003 approach, and I suspect the reason for the lack of SQL 2003 UNDER 
supertype support is that there hasn't been sufficient demand to justify 
implementing it.  This isn't a commonly used feature of Oracle or DB2.

I would say that while there are some object-relational aspects to SQL 2003, 
the overlap between those and the PostgreSQL model is non-existent for 
practical purposes.  I have actually really come to like the PostgreSQL model. 

Best Wishes,
Chris Travers

Thanks.

It sounds like Object-relational is decently supported by Postgres but there 
isn't much of a standard to follow.

Do you know whether there are any efficiency issues in Object-relational?  For 
example, has anyone benchmarked whether queries on an INHERITS table are more 
or less efficient than the equivalent using a JOIN and conventional table 
design?

-Will



-- 
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] problem with distinct not distincting...

[Susan Cassidy]

Are you sure that one of those entries doesn't have a trailing space?

Susan

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John Beynon
Sent: Wednesday, October 17, 2012 3:48 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] problem with distinct not distincting...

I have a pretty basic query;

select distinct on (name) name, length(name) from drugs where customer_id IS 
NOT NULL order by name;

which I'd expect to only return me a single drug name if there are duplicates, 
yet I get

name | length
==
Roaccutane | 10
Roaccutane | 10

table encoding is UTF8...

I'm scratching my head!

Thanks,

John.


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




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

[GENERAL] pg_stats in 9.2

[Guillaume Lelarge]

Hi,

I try to understand the new columns in pg_stats in 9.2, but I'm kinda
puzzled by the results.

Here is the test case I'm working on:

CREATE TABLE t1(c1 integer, c2 integer[]);
INSERT INTO t1 select 1, '{4}';
INSERT INTO t1 select 2, '{5}';
INSERT INTO t1 select 3, '{6}';
ANALYZE t1;

SELECT * FROM pg_stats WHERE tablename='t1';
-[ RECORD 1 ]--+---
schemaname | public
tablename  | t1
attname| c1
inherited  | f
null_frac  | 0
avg_width  | 4
n_distinct | -1
most_common_vals   | 
most_common_freqs  | 
histogram_bounds   | {1,2,3}
correlation| 1
most_common_elems  | 
most_common_elem_freqs | 
elem_count_histogram   | 

This record seems good to me. It's a scalar value, and
most_common_elems, most_common_elem_freqs, and elem_count_histogram are
NULL. Fine with me.

Now the second record:

-[ RECORD 2 ]--+---
schemaname | public
tablename  | t1
attname| ahah
inherited  | f
null_frac  | 0
avg_width  | 25
n_distinct | -1
most_common_vals   | 
most_common_freqs  | 
histogram_bounds   | {"{4}","{5}","{6}"}
correlation| 1
most_common_elems  | {4,5,6}
most_common_elem_freqs |
{0.33,0.33,0.33,0.33,0.33,0}
elem_count_histogram   |
{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}

most_common_elems seems right (all the distinct values in my arrays).
But I fail to understand why I have so many items in
most_common_elem_freqs array. I was expecting only 3, but got 6. Why?
And if I make the sum, I'm way above 1. Not sure why.

Finally, he elem_count_histogram column value doesn't make any sense to
me.

Anyone care to explain all this to me? :)

Thanks.

Regards.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com



-- 
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] problem with distinct not distincting...

[Susan Cassidy]

I see you have the length included.

Perhaps, UTF8 characters in one and ascii in the other?

Susan

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Susan Cassidy
Sent: Wednesday, October 17, 2012 8:27 AM
To: John Beynon; pgsql-general@postgresql.org
Subject: Re: [GENERAL] problem with distinct not distincting...

Are you sure that one of those entries doesn't have a trailing space?

Susan

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John Beynon
Sent: Wednesday, October 17, 2012 3:48 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] problem with distinct not distincting...

I have a pretty basic query;

select distinct on (name) name, length(name) from drugs where customer_id IS 
NOT NULL order by name;

which I'd expect to only return me a single drug name if there are duplicates, 
yet I get

name | length
==
Roaccutane | 10
Roaccutane | 10

table encoding is UTF8...

I'm scratching my head!

Thanks,

John.


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




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




-- 
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] pg_stats in 9.2

[Tom Lane]

Guillaume Lelarge  writes:
> Anyone care to explain all this to me? :)

Try the stats-slot type specifications in
src/include/catalog/pg_statistic.h

regards, tom lane


-- 
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] transaction log file "000000010000097600000051" could not be archived: too many failures

[Mathew Thomas]

Thanks...Removing the .ready files worked...

Mathew

On Tue, Oct 16, 2012 at 11:25 PM, Tom Lane  wrote:

> Mathew Thomas  writes:
> > I am getting the following error in my postgresql log file.
>
> > cp: cannot stat `pg_xlog/000109760051': No such file or
> > directory
>
> If there's a .ready file corresponding to that, remove it.
> I'm not entirely sure how you could have ended up with a .ready file
> but not the base file, but that seems the only explanation of this
> symptom.
>
> regards, tom lane
>

-- 
 

DISCLAIMER:

Please note that this message and any attachments may contain confidential 
and proprietary material and information and are intended only for the use 
of the intended recipient(s). If you are not the intended recipient, you 
are hereby notified that any review, use, disclosure, dissemination, 
distribution or copying of this message and any attachments is strictly 
prohibited. If you have received this email in error, please immediately 
notify the sender and delete this e-mail , whether electronic or printed. 
Please also note that any views, opinions, conclusions or commitments 
expressed in this message are those of the individual sender and do not 
necessarily reflect the views of *Ver sé Innovation Pvt Ltd*.

Re: [GENERAL] problem with distinct not distincting...

[Tom Lane]

John Beynon  writes:
> I have a pretty basic query;
> select distinct on (name) name, length(name) from
> drugs
> where customer_id IS NOT NULL
> order by name;

> which I'd expect to only return me a single drug name if there are
> duplicates, yet I get

> name | length
> ==
> Roaccutane | 10
> Roaccutane | 10

> table encoding is UTF8...

> I'm scratching my head!

Yeah, me too.  What PG version is this exactly?  What does EXPLAIN
show for the query?  Can you extract a self-contained test case?

regards, tom lane


-- 
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] database corruption questions

[Daniel Serodio (lists)]

Craig Ringer wrote:

On 10/14/2012 05:53 AM, Heine Ferreira wrote:

Hi

Are there any best practices for avoiding database
corruption?


* Maintain rolling backups with proper ageing. For example, keep one a 
day for the last 7 days, then one a week for the last 4 weeks, then 
one a month for the rest of the year, then one a year.

What kind of "rolling backups"? From pg_basebackup?


* Use warm standby with log shipping and/or replication to maintain a 
live copy of the DB.


* If you want point-in-time recovery, keep a few days or weeks worth 
of WAL archives and a basebackup around. That'll help you recover from 
those "oops I meant DROP TABLE unimportant; not DROP TABLE 
vital_financial_records;" issues.


* Keep up to date with the latest PostgreSQL patch releases. Don't be 
one of those people still running 9.0.0 when 9.0.10 is out.
The problem is that updating the database usually results in downtime. 
Or can the downtime be avoided in a replication scenario?


* Plug-pull test your system when you're testing it before going live. 
Put it under load with something like pgbench, then literally pull the 
plug out. If your database doesn't come back up fine you have 
hardware, OS or configuration problems.


* Don't `kill -9` the postmaster. It should be fine, but it's still 
not smart.


* ABSOLUTELY NEVER DELETE postmaster.pid

* Use good quality hardware with proper cooling and a good quality 
power supply. If possible, ECC RAM is a nice extra.


* Never, ever, ever use cheap SSDs. Use good quality hard drives or 
(after proper testing) high end SSDs. Read the SSD reviews 
periodically posted on this mailing list if considering using SSDs. 
Make sure the SSD has a supercapacitor or other reliable option for 
flushing its write cache on power loss. Always do repeated plug-pull 
testing when using SSDs.


* Use a solid, reliable file system. zfs-on-linux, btrfs, etc are not 
the right choices for a database you care about. Never, ever, ever use 
FAT32.


* If on Windows, do not run an anti-virus program on your
database server. Nobody should be using it for other things or running 
programs on it anyway.


* Avoid RAID 5, mostly because the performance is terrible, but also 
because I've seen corruption issues with rebuilds from parity on 
failing disks.


* Use a good quality hardware RAID controller with a battery backup 
cache unit if you're using spinning disks in RAID. This is as much for 
performance as reliability; a BBU will make an immense difference to 
database performance.


* If you're going to have a UPS (you shouldn't need one as your system 
should be crash-safe), don't waste your money on a cheap one. Get a 
good online double-conversion unit that does proper power filtering. 
Cheap UPSs are just a battery with a fast switch, they provide no 
power filtering and what little surge protection they offer is done 
with a component that wears out after absorbing a few surges, becoming 
totally ineffective. Since your system should be crash-safe a cheap 
UPS will do nothing for corruption protection, it'll only help with 
uptime.


--
Craig Ringer


Thanks,
Daniel Serodio


--
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] problem with distinct not distincting...

[David Johnston]

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of John Beynon
> Sent: Wednesday, October 17, 2012 6:48 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] problem with distinct not distincting...
> 
> I have a pretty basic query;
> 
> select distinct on (name) name, length(name) from drugs where
> customer_id IS NOT NULL order by name;
> 
> which I'd expect to only return me a single drug name if there are
duplicates,
> yet I get
> 
> name | length
> ==
> Roaccutane | 10
> Roaccutane | 10
> 
> table encoding is UTF8...
> 
> I'm scratching my head!
> 
> Thanks,
> 
> John.
> 

So, the following returns one record as expected on 9.0.3:

SELECT DISTINCT ON (f) f, length(l)
FROM (VALUES ('David','Johnston'),('David','Smith')) x (f, l)
ORDER BY f

Try:

SELECT name, count(*) FROM drugs where customer_id IS NOT NULL GROUP BY name

To see whether the GROUP BY logic considers the names identical.

Using "name" as a column name also sometimes has issues so maybe try giving
it an alias:

SELECT ... FROM (SELECT name AS customer_name FROM drugs WHERE ) AS
drug_aliased ...

DISTINCT ON has uses but I try to avoid it myself.  In this specific case
the "ON" is redundant since a simple DISTINCT will give you the same
results. 

You also need to provide the PostgreSQL version and possibly server
platform.

David J.




-- 
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] allow servers to access to the same data

[Daniel Serodio (lists)]

Tulio wrote:
You can use a stream replication in hot standby (native) to have the 
same data and access in both (but not update and insert in the slave, 
just select)
and create a virtual IP using heartbeat.. configuring a master to use 
some IP (virtual) and when this lost the IP, the second server (slave) 
will be use this IP..

and will allow update and insert...

is it that you want?
I've come across a few mentions of Heartbeat being used for PostgreSQL 
failover, do have any links to more information about this?



Em 17/10/2012 10:20, GMAIL escreveu:
it's possible to access the same data from two different servers. the 
two servers have the same IP and not run simultaneously


Thanks in advance,
Daniel Serodio

Re: [GENERAL] problem with distinct not distincting...

[Merlin Moncure]

On Wed, Oct 17, 2012 at 5:48 AM, John Beynon  wrote:
> I have a pretty basic query;
>
> select distinct on (name) name, length(name) from
> drugs
> where customer_id IS NOT NULL
> order by name;
>
> which I'd expect to only return me a single drug name if there are
> duplicates, yet I get
>
> name | length
> ==
> Roaccutane | 10
> Roaccutane | 10
>
> table encoding is UTF8...
>
> I'm scratching my head!

as a sanity check, try:

select distinct on (name) name, length(name), md5(name) from
drugs
where customer_id IS NOT NULL
order by name;

merlin


-- 
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] allow servers to access to the same data

[Tulio]

  
  
Sorry Daniel, but I don't have..
and I was needing test this I was searching other tutorial... 
and was configuring ans testing..

are you brazilian.. not?
read this.. http://www.hardware.com.br/tutoriais/drbd-heartbeat-samba/pagina2.html
can be usefull for you..
I don't know to much.. cause I'm stating in DB.. (aproximately one
year)
but if can I help in something.. send me..


Em 17/10/2012 14:53, Daniel Serodio (lists) escreveu:

  
  Tulio wrote:
  

You can use a stream replication in hot standby (native) to have
the same data and access in both (but not update and insert in
the slave, just select) 
and create a virtual IP using heartbeat.. configuring a master
to use some IP (virtual) and when this lost the IP, the second
server (slave) will be use this IP..
and will allow update and insert...

is it that you want?
  
  I've come across a few mentions of Heartbeat being used for
  PostgreSQL failover, do have any links to more information about
  this?
  
   Em 17/10/2012 10:20, GMAIL escreveu:
it's

  possible to access the same data from two different servers.
  the two servers have the same IP and not run simultaneously 

  
  
  Thanks in advance,
  Daniel Serodio

  

Re: [GENERAL] allow servers to access to the same data

[Alan Hodgson]

> is it that you want?
> I've come across a few mentions of Heartbeat being used for PostgreSQL
> failover, do have any links to more information about this?

If you're going to use Heartbeat on a 2-server setup, you should use DRBD for 
the replication, not the PostgreSQL replication. DRBD basically does the 
equivalent of RAID-1 mirroring between 2 servers.

http://www.linuxjournal.com/article/9074 is dated but probably still covers 
everything you need to do to make it work. 

If you have questions about setting this up, you should find a linux clustering 
group to ask. It can be quite complex if you aren't very familiar with Linux 
system administration, and this is not a good forum for the followups.


-- 
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] allow servers to access to the same data

[Shaun Thomas]

On 10/17/2012 12:53 PM, Daniel Serodio (lists) wrote:


I've come across a few mentions of Heartbeat being used for PostgreSQL
failover, do have any links to more information about this?


This was the subject of my talk at PG Open this year. I've got the 
entire PDF of slides, liner notes, and instructions on the Postgres Wiki:


http://wiki.postgresql.org/wiki/Postgres_Open_2012

Full link to PDF:

http://wiki.postgresql.org/images/0/07/Ha_postgres.pdf

It's a very cut-down version of the approach we've used successfully for 
a while.


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
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] PostgreSQL training recommendations?

[Vincent Veyron]

Le jeudi 18 octobre 2012 à 01:42 +1100, Chris Angelico a écrit :
>  Is it possible somehow to claim that reading this list is
> vital to your work, and therefore the 5 hours a week you spend
> answering other threads (in order to repay the community) is a
> legitimate work expense? :)
> 


The ratio of benefits to costs in my case is close to infinity : I have
no formal training in computer programming, so I learned practically
everything on lists (plus a few books and a lot of documentation), and
have been making a leaving out of it for fifteen years.

I consider it essential to read them, to see what are real life
situations and the usually numerous possible answers, many of which one
person would not know about; it's like training for a professional
athlete, and one has to practice every day.

Also, on numerous occasions, some thread I followed out of interest lead
me to a very suitable solution for a problem at hand within the next few
days of work : many hours were saved that way.

I could go on, but in short vital is the right word I'd say.

(I'll just mention that I am in constant awe at the level of expertise
dispensed in this particular list)

-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des assurances sinistres et des dossiers contentieux pour 
le service juridique



-- 
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] database corruption questions

[Daniel Serodio]

Craig Ringer wrote:

On 10/14/2012 05:53 AM, Heine Ferreira wrote:

Hi

Are there any best practices for avoiding database
corruption?


* Maintain rolling backups with proper ageing. For example, keep one a 
day for the last 7 days, then one a week for the last 4 weeks, then 
one a month for the rest of the year, then one a year.

What kind of "rolling backups"? From pg_basebackup?


* Use warm standby with log shipping and/or replication to maintain a 
live copy of the DB.


* If you want point-in-time recovery, keep a few days or weeks worth 
of WAL archives and a basebackup around. That'll help you recover from 
those "oops I meant DROP TABLE unimportant; not DROP TABLE 
vital_financial_records;" issues.


* Keep up to date with the latest PostgreSQL patch releases. Don't be 
one of those people still running 9.0.0 when 9.0.10 is out.
The problem is that updating the database usually results in downtime. 
Or can the downtime be avoided in a replication scenario?


* Plug-pull test your system when you're testing it before going live. 
Put it under load with something like pgbench, then literally pull the 
plug out. If your database doesn't come back up fine you have 
hardware, OS or configuration problems.


* Don't `kill -9` the postmaster. It should be fine, but it's still 
not smart.


* ABSOLUTELY NEVER DELETE postmaster.pid

* Use good quality hardware with proper cooling and a good quality 
power supply. If possible, ECC RAM is a nice extra.


* Never, ever, ever use cheap SSDs. Use good quality hard drives or 
(after proper testing) high end SSDs. Read the SSD reviews 
periodically posted on this mailing list if considering using SSDs. 
Make sure the SSD has a supercapacitor or other reliable option for 
flushing its write cache on power loss. Always do repeated plug-pull 
testing when using SSDs.


* Use a solid, reliable file system. zfs-on-linux, btrfs, etc are not 
the right choices for a database you care about. Never, ever, ever use 
FAT32.


* If on Windows, do not run an anti-virus program on your
database server. Nobody should be using it for other things or running 
programs on it anyway.


* Avoid RAID 5, mostly because the performance is terrible, but also 
because I've seen corruption issues with rebuilds from parity on 
failing disks.


* Use a good quality hardware RAID controller with a battery backup 
cache unit if you're using spinning disks in RAID. This is as much for 
performance as reliability; a BBU will make an immense difference to 
database performance.


* If you're going to have a UPS (you shouldn't need one as your system 
should be crash-safe), don't waste your money on a cheap one. Get a 
good online double-conversion unit that does proper power filtering. 
Cheap UPSs are just a battery with a fast switch, they provide no 
power filtering and what little surge protection they offer is done 
with a component that wears out after absorbing a few surges, becoming 
totally ineffective. Since your system should be crash-safe a cheap 
UPS will do nothing for corruption protection, it'll only help with 
uptime.


--
Craig Ringer


Thanks,
Daniel Serodio


--
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] problem with distinct not distincting...

[John Beynon]

I just managed to solve the problem infact.

The trailing 'e' character on the name was different for one row. All
my tools, (pgadmin and the source data in openoffice) showed the same
'e' character but psql showed it as different character...

Thanks for all taking the time to read / answer. It stumped me for a while!

John.

On Wed, Oct 17, 2012 at 4:01 PM, David Johnston  wrote:
>> -Original Message-
>> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
>> ow...@postgresql.org] On Behalf Of John Beynon
>> Sent: Wednesday, October 17, 2012 6:48 AM
>> To: pgsql-general@postgresql.org
>> Subject: [GENERAL] problem with distinct not distincting...
>>
>> I have a pretty basic query;
>>
>> select distinct on (name) name, length(name) from drugs where
>> customer_id IS NOT NULL order by name;
>>
>> which I'd expect to only return me a single drug name if there are
> duplicates,
>> yet I get
>>
>> name | length
>> ==
>> Roaccutane | 10
>> Roaccutane | 10
>>
>> table encoding is UTF8...
>>
>> I'm scratching my head!
>>
>> Thanks,
>>
>> John.
>>
>
> So, the following returns one record as expected on 9.0.3:
>
> SELECT DISTINCT ON (f) f, length(l)
> FROM (VALUES ('David','Johnston'),('David','Smith')) x (f, l)
> ORDER BY f
>
> Try:
>
> SELECT name, count(*) FROM drugs where customer_id IS NOT NULL GROUP BY name
>
> To see whether the GROUP BY logic considers the names identical.
>
> Using "name" as a column name also sometimes has issues so maybe try giving
> it an alias:
>
> SELECT ... FROM (SELECT name AS customer_name FROM drugs WHERE ) AS
> drug_aliased ...
>
> DISTINCT ON has uses but I try to avoid it myself.  In this specific case
> the "ON" is redundant since a simple DISTINCT will give you the same
> results.
>
> You also need to provide the PostgreSQL version and possibly server
> platform.
>
> David J.
>
>



-- 
John Beynon
Kyanmedia Ltd.
Direct line: 01483 405210
http://kyan.com

Registered in England. Company number: 4575679
Registered Office: 171 High Street, Guildford GU1 3AJ


-- 
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] allow servers to access to the same data

[Daniel Serodio (lists)]

Shaun Thomas wrote:

On 10/17/2012 12:53 PM, Daniel Serodio (lists) wrote:


I've come across a few mentions of Heartbeat being used for PostgreSQL
failover, do have any links to more information about this?


This was the subject of my talk at PG Open this year. I've got the 
entire PDF of slides, liner notes, and instructions on the Postgres Wiki:


http://wiki.postgresql.org/wiki/Postgres_Open_2012

Full link to PDF:

http://wiki.postgresql.org/images/0/07/Ha_postgres.pdf

It's a very cut-down version of the approach we've used successfully 
for a while.



That's great, thanks for the links.

Regards,
Daniel Serodio


--
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] problem with distinct not distincting...

[Chris Angelico]

On Thu, Oct 18, 2012 at 2:32 AM, John Beynon  wrote:
> I just managed to solve the problem infact.
>
> The trailing 'e' character on the name was different for one row. All
> my tools, (pgadmin and the source data in openoffice) showed the same
> 'e' character but psql showed it as different character...
>
> Thanks for all taking the time to read / answer. It stumped me for a while!

Cool! That's where the sanity check Merlin suggested comes in really
handy. You can manually key in what you think it's showing, and
compare with the two rows:

sikorsky@sikorsky:~$ psql -c "select md5('Roaccutane')"
   md5
--
 70f818454267c719ed612f50fe563f64
(1 row)

Anything that shows a different hash is clearly different... somehow.

ChrisA


-- 
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] allow servers to access to the same data

[Craig Ringer]

Please reply to the mailing list, not directly to me.

Reply follows below.

On 10/17/2012 10:46 PM, GMAIL wrote:
i want that the main pc save two identical databases, the first 
database will be saved on local hard drive and the second database 
will be saved on a nas.
if the main pc has a failover, a second pc will be turned up with the 
same ip and access to the database on the nas


How do you intend to get the main pc to save two identical databases?

I wouldn't recommend running Pg off a NAS file system like NFS or CIFS.

This plan is fragile and failure prone. I very strongly recommend that 
you use replication instead. See


http://www.postgresql.org/docs/current/static/high-availability.html 


http://wiki.postgresql.org/wiki/Shared_Storage

--
Craig Ringer

Re: [GENERAL] database corruption questions

[Craig Ringer]

On 10/18/2012 01:06 AM, Daniel Serodio wrote:

Craig Ringer wrote:

On 10/14/2012 05:53 AM, Heine Ferreira wrote:

Hi

Are there any best practices for avoiding database
corruption?


* Maintain rolling backups with proper ageing. For example, keep one a
day for the last 7 days, then one a week for the last 4 weeks, then
one a month for the rest of the year, then one a year.

What kind of "rolling backups"? From pg_basebackup?


I'd recommend good old `pg_dump`, that way you're not assuming that your 
cluster's on-disk format is intact and happy. Regular dumps will also 
help detect any damage that might've crept in from file system 
corruption, HDD/RAID faults, etc. Not that that should happen, but we're 
talking preventative action here.


I elaborated somewhat here:


http://blog.ringerc.id.au/2012/10/avoiding-postgresql-database-corruption.html

Alternately, check out barman: http://www.pgbarman.org/ . I haven't 
tried it yet, but it looks very promising. I'd still want to take 
periodic dumps, as I'm reluctant to rely on `pg_basebackup` style 
cluster copies alone.



* Keep up to date with the latest PostgreSQL patch releases. Don't be one of 
those people still running 9.0.0 when 9.0.10 is out.

The problem is that updating the database usually results in downtime. Or can 
the downtime be avoided in a replication scenario?


Patches within the same minor release require extremely minimal downtime.

- Stop server
- Install new binaries
- Start server

How much downtime will a data corruption bug (yes, they've happened) 
that's fixed in a new version cost you if you don't patch and it bites 
you? Or a bug that causes a server crash and restart? Plan downtime, so 
you don't have potentially much longer unplanned downtime at the worst 
possible time.


You can do zero-downtime minor updates using hot standby and standby 
promotion; see http://www.repmgr.org/ .


Updating to a new major release is a bigger job, but that's not what I'm 
talking about.



BTW, please trim your replies to quote just the relevant context.

--
Craig Ringer


--
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] 9.1/9.2 SERIALIZABLE: expected serialization failure between INSERT and SELECT not encountered

[Craig Ringer]

On 10/17/2012 04:16 PM, Albe Laurenz wrote:

Why? They can be serialized. The outcome would be exactly the same
if session 2 completed before session 1 began.


Hmm. Good point; so long as *either* ordering is valid it's fine, it's 
only when *both* orderings are invalid that a serialization failure 
would occur. For some reason I had myself thinking that if a conflict 
could occur in either ordering the tx would fail, which wouldn't really 
be desirable and isn't how it works.


BTW, the issue with the underlying question is that their "name" column 
is unique. They expected to get a serialization failure on duplicate 
insert into "name", not a unique constraint violation. The question 
wasn't "why doesn't this fail" but "Why does this fail with a different 
error than I expected". Not that the question made that particularly clear.


--
Craig Ringer


--
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] Improve MMO Game Performance

[Chris Angelico]

On Mon, Oct 15, 2012 at 7:16 PM, Albe Laurenz  wrote:
> - Set fsync=off and hope you don't crash.

Ouch. I might consider that for a bulk import operation or something,
but not for live usage. There's plenty else can be done without
risking data corruption.

ChrisA


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