[GENERAL] Cluster, repmgr, pgbouncer, pgpool, ha proxy, virtual IP, replication, failover and load balance

[Juliano]

Hi guys

> I would like to deploy load balance on Postgres master/slave(RW/R) servers 
> and also use a virtual IP on this cluster
>
> I currently have Postgres 9.4 using Repmgr for replication and manual 
> failover to avoid split-brain problems and save a witness server. Also, there 
> is Pgbouncer connection pooling working in session mode
>
> So, what is recommended to use in my current environment?
>
> Is Pgpool the best solution to use with Repmgr+Pgbouncer? In this case, 
> should I replace Pgbouncer for Pgpool or use both? What about HA proxy?
>
> I have never configured a virtual IP to cluster, should I use Keepalived, 
> Heartbeat or any other solution?
>
> Here is the image of what is in my mind: https://ibb.co/fZFebk
> [cluster.jpg]
>
> Thanks in advance for any help!

Re: [GENERAL] cluster question

[Alex Samad]

On 17 August 2017 at 10:51, Ian Barwick  wrote:

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


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



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

Re: [GENERAL] cluster question

[Ian Barwick]

On 08/16/2017 02:41 PM, Alex Samad wrote:
(...)
>
> okay think I have it setup, but when i do a switch over it gets stuck here.
>
>
>
> NOTICE: STANDBY PROMOTE successful
> NOTICE: Executing pg_rewind on old master server
> NOTICE: 3 files copied to /var/lib/pgsql/9.6/data
> NOTICE: restarting server using '/usr/pgsql-9.6/bin/pg_ctl  -w -D 
/var/lib/pgsql/9.6/data -m fast restart'
> pg_ctl: PID file "/var/lib/pgsql/9.6/data/postmaster.pid" does not exist
> Is server running?
> starting server anyway
> NOTICE: STANDBY FOLLOW successful

From the repmgr README:

>> You must ensure that following a server start using `pg_ctl`, log output
>> is not send to STDERR (the default behaviour). If logging is not configured,
>> we recommend setting `logging_collector=on` in `postgresql.conf` and
>> providing an explicit `-l/--log` setting in `repmgr.conf`'s `pg_ctl_options`
>> parameter.

i.e. when the old primary is restarted with:

/usr/pgsql-9.6/bin/pg_ctl  -w -D /var/lib/pgsql/9.6/data -m fast restart

the calling process hangs, waiting for logging output from pg_ctl.
In "repmgr.conf" set "pg_ctl_options" to something like:

pg_ctl_options='-l /path/to/log'


Regards

Ian Barwick

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


--
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] cluster question

[Alex Samad]

On 15 August 2017 at 16:35, Andreas Kretschmer 
wrote:

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



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


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

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






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



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

Re: [GENERAL] cluster question

[Andreas Kretschmer]

Am 15.08.2017 um 05:15 schrieb Alex Samad:

Hi

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

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


Is that right? Sort of makes sense I guess



That's one way to solve that problem. If you are using repmgr/repmgrd 
you can call own commands on events like failover. And, if you are using 
pgbouncer or connection-pooling you can change the config for pgbouncer 
(redefine the databases and there connection strings) and reload 
pgbouncer. You can do that with the event-notification commands defined 
in your repmgr-config.
Other solution: with some Java-drivers you can define several databases 
and checks (if the database read-only or rw), the next PostgreSQL 10 
will offer a similar feature.


PS.: please don't top-posting.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



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

Re: [GENERAL] cluster question

[Alex Samad]

Hi

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

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

Is that right? Sort of makes sense I guess

A

On 14 August 2017 at 16:47, Andreas Kretschmer 
wrote:

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

Re: [GENERAL] cluster question

[Andreas Kretschmer]

On 14 August 2017 08:39:54 GMT+02:00, Alex Samad  wrote:
>Hi
>
>I have setup a streaming replicating cluster, with a hot standby.
>
>Now I would like to change the RW to hot standby and change the hot
>standby
>to be the RW server.
>
>Is it just a matter of updating recover.conf file ?
>
>Alex

I would suggest you repmgr, with this tool you can do "repmgr standby 
switchover" to perform such tasks.

Regards, Andreas.


-- 
2ndQuadrant - The PostgreSQL Support Company


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

[GENERAL] cluster question

[Alex Samad]

Hi

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

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

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

Alex

Re: [GENERAL] cluster on brin indexes?

[Alvaro Herrera]

Samuel Williams wrote:
> I see this, but no follow up:
> 
> https://www.postgresql.org/message-id/CAEepm%3D2LUCLZ2J4cwPv5DisHqD9BE_AXnqHGqf0Tj-cvtiqVcQ%40mail.gmail.com
> 
> So, is it possible or not?

The general idea seems like it should be doable, but I haven't looked at
it in detail.  Contributions welcome.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[GENERAL] cluster on brin indexes?

[Samuel Williams]

I see this, but no follow up:

https://www.postgresql.org/message-id/CAEepm%3D2LUCLZ2J4cwPv5DisHqD9BE_AXnqHGqf0Tj-cvtiqVcQ%40mail.gmail.com

So, is it possible or not?


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

[GENERAL] cluster on table rewrite

[Jeff Janes]

I need to add a new column to my largest table, something like:

alter table foo add column col_15 text not null default 'foobar';

I am tempted to add the column as NULL, and then use coalesce and nullif in
the client code to re-interpret NULL as being the value 'foobar'.  But I
think that that would be penny wise and pound foolish, so am willing to
bite the bullet of doing a table rewrite.

But while I am rewriting it anyway, is there a way to get it to re-CLUSTER
on the cluster index, as one operation?  The rewrite caused by the ADD
COLUMN doesn't automatically cluster.

The closest I can hit upon is to do something like:

create table foo_new as select *, 'foobar'::text as col_15 from foo order
by col_8;

But then I have to manually juggle renaming tables and foreign key
constraints and such.

Is there a better way?

Cheers,

Jeff

Re: [GENERAL] Cluster on NAS and data center.

[Krzysztof Kaczkowski]

Hello,

Thanks to emails, we have achieved what we wanted. This is what we’ve done:

Compilation:

CFLAGS="-mx32 -fexcess-precision=standard -O2"

CXXFLAGS="-mx32"

./configure --without-zlib --disable-float8-byval --without-readline
--host=x86_64-linux-gnux32

We also have installed libx32.

Right now we received cluster that is fully manageable from both systems.
Anyone see something dangerous with this compilation?

Re: [GENERAL] Cluster on NAS and data center.

[Melvin Davidson]

On Mon, Jul 4, 2016 at 10:01 AM, Adrian Klaver 
wrote:

> On 07/04/2016 02:35 AM, Krzysztof Kaczkowski wrote:
>
>> Hello everyone,
>>
>> Right now we have PostgreSQL on Windows Server (main data center) and
>> cluster is placed on NAS. We have emergency data center on UNIX
>> architecture. We want that emergency data center could continue work on
>> PostgreSQL cluster that has been used by Windows PostgreSQL.
>>
>> We know that standard PostgreSQL is not able to use cluster created on
>> different OS. We think that recompilation PostgreSQL with some specific
>> flags. This should give us compatibility of cluster on different
>> Systems. We see a small differences in cluster files on binary level.
>> Can You help us pick proper compilation flags?
>>
>>
> If that where possible tools like pg_upgrade would be able to upgrade
> between OSes.
>
> What do you want the role of the UNIX data center to be, a continuous
> standby, a place where you store backups, something else?
>
>
> --
> Adrian Klaver
> adrian.kla...@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
>

Even if this were possible, it happens to be a very bad idea. The whole
concept of a backup/failover system is that it is a duplicate of the
production. So even if
you think you have it working, you are just asking for trouble when the
time comes to do a failover. Seriously, decide on one O/S and use it.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Cluster on NAS and data center.

[Adrian Klaver]

On 07/04/2016 02:35 AM, Krzysztof Kaczkowski wrote:

Hello everyone,

Right now we have PostgreSQL on Windows Server (main data center) and
cluster is placed on NAS. We have emergency data center on UNIX
architecture. We want that emergency data center could continue work on
PostgreSQL cluster that has been used by Windows PostgreSQL.

We know that standard PostgreSQL is not able to use cluster created on
different OS. We think that recompilation PostgreSQL with some specific
flags. This should give us compatibility of cluster on different
Systems. We see a small differences in cluster files on binary level.
Can You help us pick proper compilation flags?



If that where possible tools like pg_upgrade would be able to upgrade 
between OSes.


What do you want the role of the UNIX data center to be, a continuous 
standby, a place where you store backups, something else?



--
Adrian Klaver
adrian.kla...@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

[GENERAL] Cluster on NAS and data center.

[Krzysztof Kaczkowski]

Hello everyone,

Right now we have PostgreSQL on Windows Server (main data center) and
cluster is placed on NAS. We have emergency data center on UNIX
architecture. We want that emergency data center could continue work on
PostgreSQL cluster that has been used by Windows PostgreSQL.

We know that standard PostgreSQL is not able to use cluster created on
different OS. We think that recompilation PostgreSQL with some specific
flags. This should give us compatibility of cluster on different Systems.
We see a small differences in cluster files on binary level. Can You help
us pick proper compilation flags?

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

2015-02-09 16:10 GMT-05:00 Jim Nasby :

> On 2/9/15 11:51 AM, Guillaume Lelarge wrote:
>
>> According to this page
>> , exception
>> 0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
>> referenced memory at 0x%08lx. The memory could not be %s. This is not of
>> much help to me.
>>
>
> In my experience that means that your data is corrupted.
>
>  I hope these additional bits of information can help someone figuring
>> out a solution to get my cluster up and running again.
>>
>> PS. I was thinking of reinstalling PGSQL over my current install but
>> keeping my PGDATA. I've done it in the past for fixing problems with
>> starting the service and it worked. What do you think?
>>
>
> You could try it, but as Guillaume Drolet mentioned I don't see this
> helping.
>
> Since this is happening on your original database, I suspect that's what's
> been corrupted. In my experience, this means you either have faulty
> hardware, or there's a misconfiguration that means fsync isn't doing what
> it's supposed to do.
>

For those interested, I reinstalled the PGSQL binaries over, keeping my
PGDATA and tablespace. It stopped the crashes mentioned in the previous
posts, at least for now. We'll see if the crashes come back.

> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Adrian Klaver]

On 02/10/2015 08:38 AM, Guillaume Drolet wrote:


Adrian, in response to your question:

2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas


So where is role 208375PT$ supposed to come from?

I found that when I stop/start/restart pgsql through the services.msc
application in Windows, this message is issued in the log file. This
makes sense since the account I use to start the services.msc
application is my admin account, 208375PT\Admlocal.

If I use instead the command line with "pg_ctl restart -U postgres", I
don't get this message in the log file.



Thanks for the explanation. I would say at this point the error is just 
noise, but it is nice to know where it came from. Seems a corruption 
issue on the master, as others have pointed out, is at the heart of the 
matter.



--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

Adrian, in response to your question:


> 2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas
>

So where is role 208375PT$ supposed to come from?

I found that when I stop/start/restart pgsql through the services.msc
application in Windows, this message is issued in the log file. This makes
sense since the account I use to start the services.msc application is my
admin account, 208375PT\Admlocal.

If I use instead the command line with "pg_ctl restart -U postgres", I
don't get this message in the log file.


2015-02-06 11:28 GMT-05:00 Adrian Klaver :

> On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
>
>> Hi,
>>
>> Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
>> my cluster doesn't work properly. I tried restarting the computer (or
>> service) a few times but I always get the same messages in my logs (it's
>> in French. If someone is willing to help me I can try to translate the
>> logs. Just ask):
>>
>
> Enter Google Translate:)
>
> First some questions:
>
> 1) What Postgres version?
>
> 2) What OS(s)? I am assuming Windows from the log info below, but we all
> know what assuming gets you.
>
> 3) Where were you backing up from and to?
>
> 4) Which cluster does not start, the master or the child you created with
> pg_basebackup?
>
>
>> 2015-02-06 07:11:38 EST LOG:  le système de bases de données a été
>> interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
>> 2015-02-06 07:11:38 EST LOG:  le système de bases de données n'a pas été
>> arrêté proprement ; restauration
>>  automatique en cours
>> 2015-02-06 07:11:38 EST LOG:  record with zero length at 24B/2C000160
>> 2015-02-06 07:11:38 EST LOG:  la ré-exécution n'est pas nécessaire
>> 2015-02-06 07:11:38 EST LOG:  le système de bases de données est prêt
>> pour accepter les connexions
>> 2015-02-06 07:11:38 EST LOG:  lancement du processus autovacuum
>> 2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas
>>
>
> So where is role 208375PT$ supposed to come from?
>
>
>> Then if I start pgAdmin I get a series of pop-ups I have to click OK to
>> to continue:
>>
>> An error has ocurred: Column not found in pgSet: "datlastsysoid"
>> An error has ocurred: Column not found in pgSet: datlastsysoid
>> An error has ocurred: Column not found in pgSet: oid
>> An error has ocurred: Column not found in pgSet: encoding
>> An error has ocurred: Column not found in pgSet: Connection to database
>> broken
>>
>
> Not sure about that this, someone more versed in pgAdmin will have to
> answer.
>
>
>> And after that, I went back to the log file and there's new information
>> added:
>>
>> 2015-02-06 07:51:05 EST LOG:  processus serveur (PID 184) a été arrêté
>> par l'exception 0x8004
>> 2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué exécutait :
>> SELECT version();
>> 2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier d'en-tête C «
>> ntstatus.h » pour une description de la valeur
>>  hexadécimale.
>>
>
> Well according to here:
>
> https://msdn.microsoft.com/en-us/library/cc704588.aspx
>
> 0x8004
> STATUS_SINGLE_STEP
>
>
> {EXCEPTION} Single Step A single step or trace operation has just been
> completed.
>
> A developer is going to have explain what that means.
>
>
>  2015-02-06 07:51:05 EST LOG:  arrêt des autres processus serveur actifs
>> 2015-02-06 07:51:05 EST ATTENTION:  arrêt de la connexion à cause de
>> l'arrêt brutal d'un autre processus serveur
>> 2015-02-06 07:51:05 EST DÉTAIL:  Le postmaster a commandé à ce processus
>> serveur d'annuler la transaction
>>  courante et de quitter car un autre processus serveur a quitté
>> anormalement
>>  et qu'il existe probablement de la mémoire partagée corrompue.
>> 2015-02-06 07:51:05 EST ASTUCE :  Dans un moment, vous devriez être
>> capable de vous reconnecter à la base de
>>  données et de relancer votre commande.
>> 2015-02-06 07:51:05 EST LOG:  processus d'archivage (PID 692) quitte
>> avec le code de sortie 1
>> 2015-02-06 07:51:05 EST LOG:  tous les processus serveur se sont
>> arrêtés, réinitialisation
>> 2015-02-06 07:51:15 EST FATAL:  le bloc de mémoire partagé pré-existant
>> est toujours en cours d'utilisation
>> 2015-02-06 07:51:15 EST ASTUCE :  Vérifier s'il n'y a pas de vieux
>> processus serveur en cours d'exécution. Si c'est le
>>  cas, fermez-les.
>>
>> I was about to try restarting postgresql using the base backup I made
>> yesterday but since this means I'll have to copy my database again (700
>> GB takes a while...) I am looking for a better solution from more
>> experienced people.
>>
>
>
> My suspicion is you copied at least partly over a running server.
>
>
>
>> Thanks a lot for helping! Guillaume
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

2015-02-09 16:10 GMT-05:00 Jim Nasby :

> On 2/9/15 11:51 AM, Guillaume Lelarge wrote:
>
>> According to this page
>> , exception
>> 0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
>> referenced memory at 0x%08lx. The memory could not be %s. This is not of
>> much help to me.
>>
>
> In my experience that means that your data is corrupted.
>

That wouldn't be too dramatic since I can start over with another base
backup. It just takes several hours... :(

What I want to achieve in the end is to transfer my production database
from one machine to another. So here's how I did before things started to
go bad:

1. On the source machine, I took a base backup using pg_basebackup
2. I installed PGSQL on the destination machine
3. I copied the backup and extracted it on the destination machine
4. I created a PGDATA environment variable pointing to 'data' from the
extracted backup. In this backup, most of my data was in a created
tablespace so I updated the junction link (in data­\pg_tblspc) to reflect
the location of the tablespace on the new machine (It seems like when using
pg_basebackup, junction links are not saved as junction links but as empty
directories).
5. I verified I could connect to my database using psql or pgadmin. It
worked.
6. I wanted to move my tablespace to pg_default so I ran ALTER DATABASE
mydb SET TABLESPACE pg_default;
7. Ran pg_basebackup on my cluster
8. Tried to connect to database, no success...

It it the right way to proceed for this kind of operation?

Thanks!



>  I hope these additional bits of information can help someone figuring
>> out a solution to get my cluster up and running again.
>>
>> PS. I was thinking of reinstalling PGSQL over my current install but
>> keeping my PGDATA. I've done it in the past for fixing problems with
>> starting the service and it worked. What do you think?
>>
>
> You could try it, but as Guillaume Drolet mentioned I don't see this
> helping.
>
> Since this is happening on your original database, I suspect that's what's
> been corrupted. In my experience, this means you either have faulty
> hardware, or there's a misconfiguration that means fsync isn't doing what
> it's supposed to do.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Jim Nasby]

On 2/9/15 11:51 AM, Guillaume Lelarge wrote:

According to this page
, exception
0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
referenced memory at 0x%08lx. The memory could not be %s. This is not of
much help to me.


In my experience that means that your data is corrupted.


I hope these additional bits of information can help someone figuring
out a solution to get my cluster up and running again.

PS. I was thinking of reinstalling PGSQL over my current install but
keeping my PGDATA. I've done it in the past for fixing problems with
starting the service and it worked. What do you think?


You could try it, but as Guillaume Drolet mentioned I don't see this 
helping.


Since this is happening on your original database, I suspect that's 
what's been corrupted. In my experience, this means you either have 
faulty hardware, or there's a misconfiguration that means fsync isn't 
doing what it's supposed to do.

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.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] Cluster seems broken after pg_basebackup

[Adrian Klaver]

On 02/09/2015 08:34 AM, Guillaume Drolet wrote:

CCing list so the information stays in the thread.



2015-02-06 18:44 GMT-05:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>:

On 02/06/2015 09:17 AM, Guillaume Drolet wrote:

Dear Adrian,

Thanks for helping me. Sorry for the lack of details, I had said to
myself I had to not forget to give these details but I hit the send
button too fast. You know how it is...

I added more info in your reply below.


 First some questions:

 1) What Postgres version?


9.3


Windows 7


 3) Where were you backing up from and to?


Backing up from my only cluster (PGDATA) on disk E, to a backup
directory on an other disk (F:) using this command:

pg_basebackup -D "F:\\db_base_backup" -Fp -Xs  -R -P
--label="basebackup20150205" --username=postgres

What's weird is that I did some successful tests last week on
the same
system (backing up, archiving, recovering) using the same procedure.
Only difference was the cluster, which was much smaller for testing
purposes, but located at the same place (i.e. E:\data) and
PostgresSQL
installed in C:\Programs\...


 4) Which cluster does not start, the master or the child
you created
 with pg_basebackup?



The master. I haven't tried the child yet. But I saw that the
message
about role "208375PT$" is in logs from before the backup too.


This is the local domain of my machine. I log onto my machine with a
local admin account and using domain name 208375PT (I didn't set
this
part of my machine, the IT guys here at work did). The thing is:
I don't
understand why it's there in the log file??


Not sure.

What are you using for an authentication method for database login?



 At this moment, for my tests I use md5 for user 'postgres' and trust for
 user 'all'.






 And after that, I went back to the log file and there's new
 information
 added:

 2015-02-06 07:51:05 EST LOG:  processus serveur (PID
184) a été
 arrêté
 par l'exception 0x8004
 2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué
 exécutait :
 SELECT version();
 2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier
d'en-tête C «
 ntstatus.h » pour une description de la valeur
   hexadécimale.


 Well according to here:

https://msdn.microsoft.com/en-us/library/cc704588.aspx

 >

 0x8004
 STATUS_SINGLE_STEP


 {EXCEPTION} Single Step A single step or trace operation
has just
 been completed.

 A developer is going to have explain what that means.




 My suspicion is you copied at least partly over a running
server.


How would that be possible? Using the pg_basebackup command I wrote
above, it is clear that I wrote the backup on disk F and not E.


I was just speculating, I would not put too much stock in it.



While writing this post, I started my backup using:

pg_ctl start -D "F:\db_basebackup"

Similar stuff happened with pgAdmin and the log (message about
symbolic
link is related to my post from yesterday. I don't know if this
could be
involved in the current problem):

2015-02-06 12:13:58 EST LOG:  le système de bases de données a été
interrompu ; dernier lancement connu à 2015-02-05 14:30:34 EST
2015-02-06 12:13:58 EST LOG:  création du répertoire manquant «
pg_xlog/archive_status » pour les journaux de transactions
2015-02-06 12:13:58 EST LOG:  la ré-exécution commence à
24B/2890
2015-02-06 12:13:58 EST LOG:  n'a pas pu supprimer le lien
symbolique «
pg_tblspc/940585 » : No such file or directory
2015-02-06 12:13:58 EST CONTEXTE :  xlog redo drop tablespace:
940585
2015-02-06 12:13:58 EST LOG:  état de restauration cohérent
atteint à
24B/29B8
2015-02-06 12:13:58 EST LOG:  ré-exécution faite à 24B/29B8
2015-02-06 12:13:58 EST LOG:  la dernière transaction a eu lieu à
2015-02-05 09:06:04.892-05 (moment de la journalisation)
2015-02-06 12:13:59 EST LOG:  le système de bases de données est
prêt
pour accepter les connexions
2015-02-06 12:13:59 EST LOG:  lancement du processus autovacuum
2015

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

Guillaume: the cluster I try to start is the one used with pg_basebackup,
not the result of the backup.

2015-02-09 12:51 GMT-05:00 Guillaume Lelarge :

> 2015-02-09 18:40 GMT+01:00 Guillaume Drolet :
>
>> I tried starting the cluster again. Once again everything looked fine at
>> the start (first three lines of this log, in English this time):
>>
>> 2015-02-09 11:40:55 EST LOG:  database system was shut down at 2015-02-06
>> 09:50:21 EST
>> 2015-02-09 11:40:55 EST LOG:  database system is ready to accept
>> connections
>> 2015-02-09 11:40:55 EST LOG:  autovacuum launcher started
>>
>> Since it seemed to work, I opened the terminal and tried connecting to
>> the database:
>>
>> C:\Users\admlocal>psql -U postgres -d mortalite
>>
>> So far so good, I got a connection:
>>
>> psql (9.3.5)
>> Attention : l'encodage console (850) diffère de l'encodage Windows (1252).
>> Les caractères 8 bits peuvent ne pas fonctionner correctement.
>> Voir la section « Notes aux utilisateurs de Windows » de la
>> page
>> référence de psql pour les détails.
>> Saisissez « help » pour l'aide.
>>
>> mortalite=#
>>
>> I tried the help command and it worked:
>>
>> mortalite=# help
>> Vous utilisez psql, l'interface en ligne de commande de PostgreSQL.
>> Saisissez:
>> \copyright pour les termes de distribution
>> \h pour l'aide-mémoire des commandes SQL
>> \? pour l'aide-mémoire des commandes psql
>> \g ou point-virgule en fin d'instruction pour exécuter la requête
>> \q pour quitter
>>
>>
>> But then when I tried to query the db, it crashed:
>>
>> mortalite=# \dt
>> la connexion au serveur a été coupée de façon inattendue
>> Le serveur s'est peut-être arrêté anormalement avant ou durant le
>> traitement de la requête.
>> La connexion au serveur a été perdue. Tentative de réinitialisation :
>> Échec.
>> !>
>>
>> And here's the rest of the log file after the crash:
>>
>> 2015-02-09 12:29:19 EST LOG:  server process (PID 2240) was terminated by
>> exception 0xC005
>> 2015-02-09 12:29:19 EST DETAIL:  Failed process was running: SELECT
>> n.nspname as "Schema",
>>   c.relname as "Name",
>>   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
>> THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
>> WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
>>   pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
>> FROM pg_catalog.pg_class c
>>  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
>> WHERE c.relkind IN ('r','')
>>   AND n.nspname <> 'pg_catalog'
>>   AND n.nspname <> 'information_schema'
>>   AND n.nspname !~ '^pg_toast'
>>   AND pg_catalog.pg_table_is_visible(c.oid)
>> ORDER BY 1,2;
>> 2015-02-09 12:29:19 EST HINT:  See C include file "ntstatus.h" for a
>> description of the hexadecimal value.
>> 2015-02-09 12:29:19 EST LOG:  terminating any other active server
>> processes
>> 2015-02-09 12:29:19 EST WARNING:  terminating connection because of crash
>> of another server process
>> 2015-02-09 12:29:19 EST DETAIL:  The postmaster has commanded this server
>> process to roll back the current transaction and exit, because another
>> server process exited abnormally and possibly corrupted shared memory.
>> 2015-02-09 12:29:19 EST HINT:  In a moment you should be able to
>> reconnect to the database and repeat your command.
>> 2015-02-09 12:29:19 EST LOG:  archiver process (PID 4576) exited with
>> exit code 1
>> 2015-02-09 12:29:19 EST LOG:  all server processes terminated;
>> reinitializing
>> 2015-02-09 12:29:29 EST FATAL:  pre-existing shared memory block is still
>> in use
>> 2015-02-09 12:29:29 EST HINT:  Check if there are any old server
>> processes still running, and terminate them.
>>
>> According to this page
>> , exception
>> 0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
>> referenced memory at 0x%08lx. The memory could not be %s. This is not of
>> much help to me.
>>
>> I hope these additional bits of information can help someone figuring out
>> a solution to get my cluster up and running again.
>>
>> PS. I was thinking of reinstalling PGSQL over my current install but
>> keeping my PGDATA. I've done it in the past for fixing problems with
>> starting the service and it worked. What do you think?
>>
>>
> The SQL query you see in your log is the result of your \dt. It should
> work. I don't see why reinstalling PostgreSQL will fix anything here.
>
> You said you did a pg_basebackup. The cluster you try to start is the
> cluster used with pg_basebackup or a restore done with the pg_basebackup?
>
> Cheers,
>>
>> Guillaume
>>
>>
>>
>>
>>
>> 2015-02-09 11:37 GMT-05:00 Guillaume Drolet :
>>
>>
>>>
>>> 2015-02-07 1:24 GMT-05:00 Guillaume Lelarge :
>>>
 Le 6 févr. 2015 17:31, "Adrian Klaver"  a
 écrit :
 >
 > On 02/06/2015 05:03 AM, Guillaume

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Lelarge]

2015-02-09 18:40 GMT+01:00 Guillaume Drolet :

> I tried starting the cluster again. Once again everything looked fine at
> the start (first three lines of this log, in English this time):
>
> 2015-02-09 11:40:55 EST LOG:  database system was shut down at 2015-02-06
> 09:50:21 EST
> 2015-02-09 11:40:55 EST LOG:  database system is ready to accept
> connections
> 2015-02-09 11:40:55 EST LOG:  autovacuum launcher started
>
> Since it seemed to work, I opened the terminal and tried connecting to the
> database:
>
> C:\Users\admlocal>psql -U postgres -d mortalite
>
> So far so good, I got a connection:
>
> psql (9.3.5)
> Attention : l'encodage console (850) diffère de l'encodage Windows (1252).
> Les caractères 8 bits peuvent ne pas fonctionner correctement.
> Voir la section « Notes aux utilisateurs de Windows » de la
> page
> référence de psql pour les détails.
> Saisissez « help » pour l'aide.
>
> mortalite=#
>
> I tried the help command and it worked:
>
> mortalite=# help
> Vous utilisez psql, l'interface en ligne de commande de PostgreSQL.
> Saisissez:
> \copyright pour les termes de distribution
> \h pour l'aide-mémoire des commandes SQL
> \? pour l'aide-mémoire des commandes psql
> \g ou point-virgule en fin d'instruction pour exécuter la requête
> \q pour quitter
>
>
> But then when I tried to query the db, it crashed:
>
> mortalite=# \dt
> la connexion au serveur a été coupée de façon inattendue
> Le serveur s'est peut-être arrêté anormalement avant ou durant le
> traitement de la requête.
> La connexion au serveur a été perdue. Tentative de réinitialisation :
> Échec.
> !>
>
> And here's the rest of the log file after the crash:
>
> 2015-02-09 12:29:19 EST LOG:  server process (PID 2240) was terminated by
> exception 0xC005
> 2015-02-09 12:29:19 EST DETAIL:  Failed process was running: SELECT
> n.nspname as "Schema",
>   c.relname as "Name",
>   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
> THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
> WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
>   pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
> FROM pg_catalog.pg_class c
>  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','')
>   AND n.nspname <> 'pg_catalog'
>   AND n.nspname <> 'information_schema'
>   AND n.nspname !~ '^pg_toast'
>   AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1,2;
> 2015-02-09 12:29:19 EST HINT:  See C include file "ntstatus.h" for a
> description of the hexadecimal value.
> 2015-02-09 12:29:19 EST LOG:  terminating any other active server processes
> 2015-02-09 12:29:19 EST WARNING:  terminating connection because of crash
> of another server process
> 2015-02-09 12:29:19 EST DETAIL:  The postmaster has commanded this server
> process to roll back the current transaction and exit, because another
> server process exited abnormally and possibly corrupted shared memory.
> 2015-02-09 12:29:19 EST HINT:  In a moment you should be able to reconnect
> to the database and repeat your command.
> 2015-02-09 12:29:19 EST LOG:  archiver process (PID 4576) exited with exit
> code 1
> 2015-02-09 12:29:19 EST LOG:  all server processes terminated;
> reinitializing
> 2015-02-09 12:29:29 EST FATAL:  pre-existing shared memory block is still
> in use
> 2015-02-09 12:29:29 EST HINT:  Check if there are any old server processes
> still running, and terminate them.
>
> According to this page
> , exception
> 0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
> referenced memory at 0x%08lx. The memory could not be %s. This is not of
> much help to me.
>
> I hope these additional bits of information can help someone figuring out
> a solution to get my cluster up and running again.
>
> PS. I was thinking of reinstalling PGSQL over my current install but
> keeping my PGDATA. I've done it in the past for fixing problems with
> starting the service and it worked. What do you think?
>
>
The SQL query you see in your log is the result of your \dt. It should
work. I don't see why reinstalling PostgreSQL will fix anything here.

You said you did a pg_basebackup. The cluster you try to start is the
cluster used with pg_basebackup or a restore done with the pg_basebackup?

Cheers,
>
> Guillaume
>
>
>
>
>
> 2015-02-09 11:37 GMT-05:00 Guillaume Drolet :
>
>
>>
>> 2015-02-07 1:24 GMT-05:00 Guillaume Lelarge :
>>
>>> Le 6 févr. 2015 17:31, "Adrian Klaver"  a
>>> écrit :
>>> >
>>> > On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
>>> >>
>>> >> Hi,
>>> >>
>>> >> Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
>>> >> my cluster doesn't work properly. I tried restarting the computer (or
>>> >> service) a few times but I always get the same messages in my logs
>>> (it's
>>> >> in

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

I tried starting the cluster again. Once again everything looked fine at
the start (first three lines of this log, in English this time):

2015-02-09 11:40:55 EST LOG:  database system was shut down at 2015-02-06
09:50:21 EST
2015-02-09 11:40:55 EST LOG:  database system is ready to accept connections
2015-02-09 11:40:55 EST LOG:  autovacuum launcher started

Since it seemed to work, I opened the terminal and tried connecting to the
database:

C:\Users\admlocal>psql -U postgres -d mortalite

So far so good, I got a connection:

psql (9.3.5)
Attention : l'encodage console (850) diffère de l'encodage Windows (1252).
Les caractères 8 bits peuvent ne pas fonctionner correctement.
Voir la section « Notes aux utilisateurs de Windows » de la page
référence de psql pour les détails.
Saisissez « help » pour l'aide.

mortalite=#

I tried the help command and it worked:

mortalite=# help
Vous utilisez psql, l'interface en ligne de commande de PostgreSQL.
Saisissez:
\copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter


But then when I tried to query the db, it crashed:

mortalite=# \dt
la connexion au serveur a été coupée de façon inattendue
Le serveur s'est peut-être arrêté anormalement avant ou durant le
traitement de la requête.
La connexion au serveur a été perdue. Tentative de réinitialisation : Échec.
!>

And here's the rest of the log file after the crash:

2015-02-09 12:29:19 EST LOG:  server process (PID 2240) was terminated by
exception 0xC005
2015-02-09 12:29:19 EST DETAIL:  Failed process was running: SELECT
n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
2015-02-09 12:29:19 EST HINT:  See C include file "ntstatus.h" for a
description of the hexadecimal value.
2015-02-09 12:29:19 EST LOG:  terminating any other active server processes
2015-02-09 12:29:19 EST WARNING:  terminating connection because of crash
of another server process
2015-02-09 12:29:19 EST DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2015-02-09 12:29:19 EST HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
2015-02-09 12:29:19 EST LOG:  archiver process (PID 4576) exited with exit
code 1
2015-02-09 12:29:19 EST LOG:  all server processes terminated;
reinitializing
2015-02-09 12:29:29 EST FATAL:  pre-existing shared memory block is still
in use
2015-02-09 12:29:29 EST HINT:  Check if there are any old server processes
still running, and terminate them.

According to this page
, exception
0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
referenced memory at 0x%08lx. The memory could not be %s. This is not of
much help to me.

I hope these additional bits of information can help someone figuring out a
solution to get my cluster up and running again.

PS. I was thinking of reinstalling PGSQL over my current install but
keeping my PGDATA. I've done it in the past for fixing problems with
starting the service and it worked. What do you think?

Cheers,

Guillaume





2015-02-09 11:37 GMT-05:00 Guillaume Drolet :

>
>
> 2015-02-07 1:24 GMT-05:00 Guillaume Lelarge :
>
>> Le 6 févr. 2015 17:31, "Adrian Klaver"  a
>> écrit :
>> >
>> > On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
>> >>
>> >> Hi,
>> >>
>> >> Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
>> >> my cluster doesn't work properly. I tried restarting the computer (or
>> >> service) a few times but I always get the same messages in my logs
>> (it's
>> >> in French. If someone is willing to help me I can try to translate the
>> >> logs. Just ask):
>> >
>> >
>> > Enter Google Translate:)
>> >
>>
>> But first, Guillaume, do yourself and everyone else a favor: turn the dam
>> log into English. Set lc_messages to 'C' in postgresql.conf.
>>
> Thanks for this! I didn't know about this great feature.
>
>
>> > First some questions:
>> >
>> > 1) What Postgres version?
>> >
>> > 2) What OS(s)? I am assuming Windows from the log info below, but we
>> all know what assuming gets you.
>> >
>> > 3) Where

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

2015-02-07 1:24 GMT-05:00 Guillaume Lelarge :

> Le 6 févr. 2015 17:31, "Adrian Klaver"  a
> écrit :
> >
> > On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
> >>
> >> Hi,
> >>
> >> Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
> >> my cluster doesn't work properly. I tried restarting the computer (or
> >> service) a few times but I always get the same messages in my logs (it's
> >> in French. If someone is willing to help me I can try to translate the
> >> logs. Just ask):
> >
> >
> > Enter Google Translate:)
> >
>
> But first, Guillaume, do yourself and everyone else a favor: turn the dam
> log into English. Set lc_messages to 'C' in postgresql.conf.
>
Thanks for this! I didn't know about this great feature.


> > First some questions:
> >
> > 1) What Postgres version?
> >
> > 2) What OS(s)? I am assuming Windows from the log info below, but we all
> know what assuming gets you.
> >
> > 3) Where were you backing up from and to?
> >
> > 4) Which cluster does not start, the master or the child you created
> with pg_basebackup?
> >
> >
> >>
> >> 2015-02-06 07:11:38 EST LOG:  le système de bases de données a été
> >> interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
> >> 2015-02-06 07:11:38 EST LOG:  le système de bases de données n'a pas été
> >> arrêté proprement ; restauration
> >>  automatique en cours
> >> 2015-02-06 07:11:38 EST LOG:  record with zero length at 24B/2C000160
> >> 2015-02-06 07:11:38 EST LOG:  la ré-exécution n'est pas nécessaire
> >> 2015-02-06 07:11:38 EST LOG:  le système de bases de données est prêt
> >> pour accepter les connexions
> >> 2015-02-06 07:11:38 EST LOG:  lancement du processus autovacuum
> >> 2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas
> >
> >
> > So where is role 208375PT$ supposed to come from?
> >
> >
> >>
> >> Then if I start pgAdmin I get a series of pop-ups I have to click OK to
> >> to continue:
> >>
> >> An error has ocurred: Column not found in pgSet: "datlastsysoid"
> >> An error has ocurred: Column not found in pgSet: datlastsysoid
> >> An error has ocurred: Column not found in pgSet: oid
> >> An error has ocurred: Column not found in pgSet: encoding
> >> An error has ocurred: Column not found in pgSet: Connection to database
> >> broken
> >
> >
> > Not sure about that this, someone more versed in pgAdmin will have to
> answer.
> >
>
> Usually you see these messages when you're using a pgadmin major release
> older than a PostgreSQL make release. For a 9.3 release, that would mean a
> pgadmin older than 1.18.
>

I'm running pgadmin 1.18.1

> >
> >>
> >> And after that, I went back to the log file and there's new information
> >> added:
> >>
> >> 2015-02-06 07:51:05 EST LOG:  processus serveur (PID 184) a été arrêté
> >> par l'exception 0x8004
> >> 2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué exécutait :
> >> SELECT version();
> >> 2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier d'en-tête C «
> >> ntstatus.h » pour une description de la valeur
> >>  hexadécimale.
> >
> >
> > Well according to here:
> >
> > https://msdn.microsoft.com/en-us/library/cc704588.aspx
> >
> > 0x8004
> > STATUS_SINGLE_STEP
> >
> >
> > {EXCEPTION} Single Step A single step or trace operation has just been
> completed.
> >
> > A developer is going to have explain what that means.
> >
> >
> >
> >> 2015-02-06 07:51:05 EST LOG:  arrêt des autres processus serveur actifs
> >> 2015-02-06 07:51:05 EST ATTENTION:  arrêt de la connexion à cause de
> >> l'arrêt brutal d'un autre processus serveur
> >> 2015-02-06 07:51:05 EST DÉTAIL:  Le postmaster a commandé à ce processus
> >> serveur d'annuler la transaction
> >>  courante et de quitter car un autre processus serveur a quitté
> >> anormalement
> >>  et qu'il existe probablement de la mémoire partagée corrompue.
> >> 2015-02-06 07:51:05 EST ASTUCE :  Dans un moment, vous devriez être
> >> capable de vous reconnecter à la base de
> >>  données et de relancer votre commande.
> >> 2015-02-06 07:51:05 EST LOG:  processus d'archivage (PID 692) quitte
> >> avec le code de sortie 1
> >> 2015-02-06 07:51:05 EST LOG:  tous les processus serveur se sont
> >> arrêtés, réinitialisation
> >> 2015-02-06 07:51:15 EST FATAL:  le bloc de mémoire partagé pré-existant
> >> est toujours en cours d'utilisation
> >> 2015-02-06 07:51:15 EST ASTUCE :  Vérifier s'il n'y a pas de vieux
> >> processus serveur en cours d'exécution. Si c'est le
> >>  cas, fermez-les.
> >>
> >> I was about to try restarting postgresql using the base backup I made
> >> yesterday but since this means I'll have to copy my database again (700
> >> GB takes a while...) I am looking for a better solution from more
> >> experienced people.
> >
> >
> >
> > My suspicion is you copied at least partly over a running server.
> >
>

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Lelarge]

Le 6 févr. 2015 17:31, "Adrian Klaver"  a écrit :
>
> On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
>>
>> Hi,
>>
>> Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
>> my cluster doesn't work properly. I tried restarting the computer (or
>> service) a few times but I always get the same messages in my logs (it's
>> in French. If someone is willing to help me I can try to translate the
>> logs. Just ask):
>
>
> Enter Google Translate:)
>

But first, Guillaume, do yourself and everyone else a favor: turn the dam
log into English. Set lc_messages to 'C' in postgresql.conf.

> First some questions:
>
> 1) What Postgres version?
>
> 2) What OS(s)? I am assuming Windows from the log info below, but we all
know what assuming gets you.
>
> 3) Where were you backing up from and to?
>
> 4) Which cluster does not start, the master or the child you created with
pg_basebackup?
>
>
>>
>> 2015-02-06 07:11:38 EST LOG:  le système de bases de données a été
>> interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
>> 2015-02-06 07:11:38 EST LOG:  le système de bases de données n'a pas été
>> arrêté proprement ; restauration
>>  automatique en cours
>> 2015-02-06 07:11:38 EST LOG:  record with zero length at 24B/2C000160
>> 2015-02-06 07:11:38 EST LOG:  la ré-exécution n'est pas nécessaire
>> 2015-02-06 07:11:38 EST LOG:  le système de bases de données est prêt
>> pour accepter les connexions
>> 2015-02-06 07:11:38 EST LOG:  lancement du processus autovacuum
>> 2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas
>
>
> So where is role 208375PT$ supposed to come from?
>
>
>>
>> Then if I start pgAdmin I get a series of pop-ups I have to click OK to
>> to continue:
>>
>> An error has ocurred: Column not found in pgSet: "datlastsysoid"
>> An error has ocurred: Column not found in pgSet: datlastsysoid
>> An error has ocurred: Column not found in pgSet: oid
>> An error has ocurred: Column not found in pgSet: encoding
>> An error has ocurred: Column not found in pgSet: Connection to database
>> broken
>
>
> Not sure about that this, someone more versed in pgAdmin will have to
answer.
>

Usually you see these messages when you're using a pgadmin major release
older than a PostgreSQL make release. For a 9.3 release, that would mean a
pgadmin older than 1.18.

>
>>
>> And after that, I went back to the log file and there's new information
>> added:
>>
>> 2015-02-06 07:51:05 EST LOG:  processus serveur (PID 184) a été arrêté
>> par l'exception 0x8004
>> 2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué exécutait :
>> SELECT version();
>> 2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier d'en-tête C «
>> ntstatus.h » pour une description de la valeur
>>  hexadécimale.
>
>
> Well according to here:
>
> https://msdn.microsoft.com/en-us/library/cc704588.aspx
>
> 0x8004
> STATUS_SINGLE_STEP
>
>
> {EXCEPTION} Single Step A single step or trace operation has just been
completed.
>
> A developer is going to have explain what that means.
>
>
>
>> 2015-02-06 07:51:05 EST LOG:  arrêt des autres processus serveur actifs
>> 2015-02-06 07:51:05 EST ATTENTION:  arrêt de la connexion à cause de
>> l'arrêt brutal d'un autre processus serveur
>> 2015-02-06 07:51:05 EST DÉTAIL:  Le postmaster a commandé à ce processus
>> serveur d'annuler la transaction
>>  courante et de quitter car un autre processus serveur a quitté
>> anormalement
>>  et qu'il existe probablement de la mémoire partagée corrompue.
>> 2015-02-06 07:51:05 EST ASTUCE :  Dans un moment, vous devriez être
>> capable de vous reconnecter à la base de
>>  données et de relancer votre commande.
>> 2015-02-06 07:51:05 EST LOG:  processus d'archivage (PID 692) quitte
>> avec le code de sortie 1
>> 2015-02-06 07:51:05 EST LOG:  tous les processus serveur se sont
>> arrêtés, réinitialisation
>> 2015-02-06 07:51:15 EST FATAL:  le bloc de mémoire partagé pré-existant
>> est toujours en cours d'utilisation
>> 2015-02-06 07:51:15 EST ASTUCE :  Vérifier s'il n'y a pas de vieux
>> processus serveur en cours d'exécution. Si c'est le
>>  cas, fermez-les.
>>
>> I was about to try restarting postgresql using the base backup I made
>> yesterday but since this means I'll have to copy my database again (700
>> GB takes a while...) I am looking for a better solution from more
>> experienced people.
>
>
>
> My suspicion is you copied at least partly over a running server.
>

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Adrian Klaver]

On 02/06/2015 09:17 AM, Guillaume Drolet wrote:

Dear Adrian,

Thanks for helping me. Sorry for the lack of details, I had said to
myself I had to not forget to give these details but I hit the send
button too fast. You know how it is...

I added more info in your reply below.


First some questions:

1) What Postgres version?


9.3




Windows 7


3) Where were you backing up from and to?


Backing up from my only cluster (PGDATA) on disk E, to a backup
directory on an other disk (F:) using this command:

pg_basebackup -D "F:\\db_base_backup" -Fp -Xs  -R -P
--label="basebackup20150205" --username=postgres

What's weird is that I did some successful tests last week on the same
system (backing up, archiving, recovering) using the same procedure.
Only difference was the cluster, which was much smaller for testing
purposes, but located at the same place (i.e. E:\data) and PostgresSQL
installed in C:\Programs\...


4) Which cluster does not start, the master or the child you created
with pg_basebackup?



The master. I haven't tried the child yet. But I saw that the message
about role "208375PT$" is in logs from before the backup too.




This is the local domain of my machine. I log onto my machine with a
local admin account and using domain name 208375PT (I didn't set this
part of my machine, the IT guys here at work did). The thing is: I don't
understand why it's there in the log file??


Not sure.

What are you using for an authentication method for database login?






And after that, I went back to the log file and there's new
information
added:

2015-02-06 07:51:05 EST LOG:  processus serveur (PID 184) a été
arrêté
par l'exception 0x8004
2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué
exécutait :
SELECT version();
2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier d'en-tête C «
ntstatus.h » pour une description de la valeur
  hexadécimale.


Well according to here:

https://msdn.microsoft.com/en-__us/library/cc704588.aspx


0x8004
STATUS_SINGLE_STEP


{EXCEPTION} Single Step A single step or trace operation has just
been completed.

A developer is going to have explain what that means.






My suspicion is you copied at least partly over a running server.


How would that be possible? Using the pg_basebackup command I wrote
above, it is clear that I wrote the backup on disk F and not E.


I was just speculating, I would not put too much stock in it.



While writing this post, I started my backup using:

pg_ctl start -D "F:\db_basebackup"

Similar stuff happened with pgAdmin and the log (message about symbolic
link is related to my post from yesterday. I don't know if this could be
involved in the current problem):

2015-02-06 12:13:58 EST LOG:  le système de bases de données a été
interrompu ; dernier lancement connu à 2015-02-05 14:30:34 EST
2015-02-06 12:13:58 EST LOG:  création du répertoire manquant «
pg_xlog/archive_status » pour les journaux de transactions
2015-02-06 12:13:58 EST LOG:  la ré-exécution commence à 24B/2890
2015-02-06 12:13:58 EST LOG:  n'a pas pu supprimer le lien symbolique «
pg_tblspc/940585 » : No such file or directory
2015-02-06 12:13:58 EST CONTEXTE :  xlog redo drop tablespace: 940585
2015-02-06 12:13:58 EST LOG:  état de restauration cohérent atteint à
24B/29B8
2015-02-06 12:13:58 EST LOG:  ré-exécution faite à 24B/29B8
2015-02-06 12:13:58 EST LOG:  la dernière transaction a eu lieu à
2015-02-05 09:06:04.892-05 (moment de la journalisation)
2015-02-06 12:13:59 EST LOG:  le système de bases de données est prêt
pour accepter les connexions
2015-02-06 12:13:59 EST LOG:  lancement du processus autovacuum
2015-02-06 12:14:42 EST LOG:  processus serveur (PID 1784) a été arrêté
par l'exception 0x8004
2015-02-06 12:14:42 EST DÉTAIL:  Le processus qui a échoué exécutait :
SELECT version();
2015-02-06 12:14:42 EST ASTUCE :  Voir le fichier d'en-tête C «
ntstatus.h » pour une description de la valeur
 hexadécimale.
2015-02-06 12:14:42 EST LOG:  arrêt des autres processus serveur actifs
2015-02-06 12:14:42 EST ATTENTION:  arrêt de la connexion à cause de
l'arrêt brutal d'un autre processus serveur
2015-02-06 12:14:42 EST DÉTAIL:  Le postmaster a commandé à ce processus
serveur d'annuler la transaction
 courante et de quitter car un autre processus serveur a quitté
anormalement
 et qu'il existe probablement de la mémoire partagée corrompue.
2015-02-06 12:14:42 EST ASTUCE :  Dans un moment, vous devriez être
capable de vous reconnecter à la base de
 données et de relancer votre commande.
2015-02-06 12:14:42 EST LOG:  tous les processus serveur se sont
arrêtés, réinitialisation


Any ideas where to go from here?


In both cases the database got to the point below, which would seem to 
indicate everything was alright.


2015-02-

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

Dear Adrian,

Thanks for helping me. Sorry for the lack of details, I had said to myself
I had to not forget to give these details but I hit the send button too
fast. You know how it is...

I added more info in your reply below.


2015-02-06 11:28 GMT-05:00 Adrian Klaver :

> On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
>
>> Hi,
>>
>> Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
>> my cluster doesn't work properly. I tried restarting the computer (or
>> service) a few times but I always get the same messages in my logs (it's
>> in French. If someone is willing to help me I can try to translate the
>> logs. Just ask):
>>
>
> Enter Google Translate:)
>

Not a big fan. I've seen bad misunderstandings happen there!

>
> First some questions:
>
> 1) What Postgres version?
>

9.3

>
> 2) What OS(s)? I am assuming Windows from the log info below, but we all
> know what assuming gets you.
>

Windows 7

>
> 3) Where were you backing up from and to?
>

Backing up from my only cluster (PGDATA) on disk E, to a backup directory
on an other disk (F:) using this command:

pg_basebackup -D "F:\\db_base_backup" -Fp -Xs  -R -P
--label="basebackup20150205" --username=postgres

What's weird is that I did some successful tests last week on the same
system (backing up, archiving, recovering) using the same procedure. Only
difference was the cluster, which was much smaller for testing purposes,
but located at the same place (i.e. E:\data) and PostgresSQL installed in
C:\Programs\...




> 4) Which cluster does not start, the master or the child you created with
> pg_basebackup?
>


The master. I haven't tried the child yet. But I saw that the message about
role "208375PT$" is in logs from before the backup too.

>
>
>> 2015-02-06 07:11:38 EST LOG:  le système de bases de données a été
>> interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
>> 2015-02-06 07:11:38 EST LOG:  le système de bases de données n'a pas été
>> arrêté proprement ; restauration
>>  automatique en cours
>> 2015-02-06 07:11:38 EST LOG:  record with zero length at 24B/2C000160
>> 2015-02-06 07:11:38 EST LOG:  la ré-exécution n'est pas nécessaire
>> 2015-02-06 07:11:38 EST LOG:  le système de bases de données est prêt
>> pour accepter les connexions
>> 2015-02-06 07:11:38 EST LOG:  lancement du processus autovacuum
>> 2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas
>>
>
> So where is role 208375PT$ supposed to come from?
>

This is the local domain of my machine. I log onto my machine with a local
admin account and using domain name 208375PT (I didn't set this part of my
machine, the IT guys here at work did). The thing is: I don't understand
why it's there in the log file??


>
>
>> Then if I start pgAdmin I get a series of pop-ups I have to click OK to
>> to continue:
>>
>> An error has ocurred: Column not found in pgSet: "datlastsysoid"
>> An error has ocurred: Column not found in pgSet: datlastsysoid
>> An error has ocurred: Column not found in pgSet: oid
>> An error has ocurred: Column not found in pgSet: encoding
>> An error has ocurred: Column not found in pgSet: Connection to database
>> broken
>>
>
> Not sure about that this, someone more versed in pgAdmin will have to
> answer.
>
>
>> And after that, I went back to the log file and there's new information
>> added:
>>
>> 2015-02-06 07:51:05 EST LOG:  processus serveur (PID 184) a été arrêté
>> par l'exception 0x8004
>> 2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué exécutait :
>> SELECT version();
>> 2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier d'en-tête C «
>> ntstatus.h » pour une description de la valeur
>>  hexadécimale.
>>
>
> Well according to here:
>
> https://msdn.microsoft.com/en-us/library/cc704588.aspx
>
> 0x8004
> STATUS_SINGLE_STEP
>
>
> {EXCEPTION} Single Step A single step or trace operation has just been
> completed.
>
> A developer is going to have explain what that means.
>
>
>  2015-02-06 07:51:05 EST LOG:  arrêt des autres processus serveur actifs
>> 2015-02-06 07:51:05 EST ATTENTION:  arrêt de la connexion à cause de
>> l'arrêt brutal d'un autre processus serveur
>> 2015-02-06 07:51:05 EST DÉTAIL:  Le postmaster a commandé à ce processus
>> serveur d'annuler la transaction
>>  courante et de quitter car un autre processus serveur a quitté
>> anormalement
>>  et qu'il existe probablement de la mémoire partagée corrompue.
>> 2015-02-06 07:51:05 EST ASTUCE :  Dans un moment, vous devriez être
>> capable de vous reconnecter à la base de
>>  données et de relancer votre commande.
>> 2015-02-06 07:51:05 EST LOG:  processus d'archivage (PID 692) quitte
>> avec le code de sortie 1
>> 2015-02-06 07:51:05 EST LOG:  tous les processus serveur se sont
>> arrêtés, réinitialisation
>> 2015-02-06 07:51:15 EST FATAL:  le bloc de mémoire partagé pré-existant
>> est toujours en cours d'utilisation
>> 2015-02-06 07:51:15 EST ASTUCE :  Vérifier s'il n'y a pas de vieux
>> processus serveur en cours d

Re: [GENERAL] Cluster seems broken after pg_basebackup

[Adrian Klaver]

On 02/06/2015 05:03 AM, Guillaume Drolet wrote:

Hi,

Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
my cluster doesn't work properly. I tried restarting the computer (or
service) a few times but I always get the same messages in my logs (it's
in French. If someone is willing to help me I can try to translate the
logs. Just ask):


Enter Google Translate:)

First some questions:

1) What Postgres version?

2) What OS(s)? I am assuming Windows from the log info below, but we all 
know what assuming gets you.


3) Where were you backing up from and to?

4) Which cluster does not start, the master or the child you created 
with pg_basebackup?




2015-02-06 07:11:38 EST LOG:  le système de bases de données a été
interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
2015-02-06 07:11:38 EST LOG:  le système de bases de données n'a pas été
arrêté proprement ; restauration
 automatique en cours
2015-02-06 07:11:38 EST LOG:  record with zero length at 24B/2C000160
2015-02-06 07:11:38 EST LOG:  la ré-exécution n'est pas nécessaire
2015-02-06 07:11:38 EST LOG:  le système de bases de données est prêt
pour accepter les connexions
2015-02-06 07:11:38 EST LOG:  lancement du processus autovacuum
2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas


So where is role 208375PT$ supposed to come from?



Then if I start pgAdmin I get a series of pop-ups I have to click OK to
to continue:

An error has ocurred: Column not found in pgSet: "datlastsysoid"
An error has ocurred: Column not found in pgSet: datlastsysoid
An error has ocurred: Column not found in pgSet: oid
An error has ocurred: Column not found in pgSet: encoding
An error has ocurred: Column not found in pgSet: Connection to database
broken


Not sure about that this, someone more versed in pgAdmin will have to 
answer.




And after that, I went back to the log file and there's new information
added:

2015-02-06 07:51:05 EST LOG:  processus serveur (PID 184) a été arrêté
par l'exception 0x8004
2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué exécutait :
SELECT version();
2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier d'en-tête C «
ntstatus.h » pour une description de la valeur
 hexadécimale.


Well according to here:

https://msdn.microsoft.com/en-us/library/cc704588.aspx

0x8004
STATUS_SINGLE_STEP


{EXCEPTION} Single Step A single step or trace operation has just been 
completed.


A developer is going to have explain what that means.



2015-02-06 07:51:05 EST LOG:  arrêt des autres processus serveur actifs
2015-02-06 07:51:05 EST ATTENTION:  arrêt de la connexion à cause de
l'arrêt brutal d'un autre processus serveur
2015-02-06 07:51:05 EST DÉTAIL:  Le postmaster a commandé à ce processus
serveur d'annuler la transaction
 courante et de quitter car un autre processus serveur a quitté
anormalement
 et qu'il existe probablement de la mémoire partagée corrompue.
2015-02-06 07:51:05 EST ASTUCE :  Dans un moment, vous devriez être
capable de vous reconnecter à la base de
 données et de relancer votre commande.
2015-02-06 07:51:05 EST LOG:  processus d'archivage (PID 692) quitte
avec le code de sortie 1
2015-02-06 07:51:05 EST LOG:  tous les processus serveur se sont
arrêtés, réinitialisation
2015-02-06 07:51:15 EST FATAL:  le bloc de mémoire partagé pré-existant
est toujours en cours d'utilisation
2015-02-06 07:51:15 EST ASTUCE :  Vérifier s'il n'y a pas de vieux
processus serveur en cours d'exécution. Si c'est le
 cas, fermez-les.

I was about to try restarting postgresql using the base backup I made
yesterday but since this means I'll have to copy my database again (700
GB takes a while...) I am looking for a better solution from more
experienced people.



My suspicion is you copied at least partly over a running server.



Thanks a lot for helping! Guillaume




--
Adrian Klaver
adrian.kla...@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

[GENERAL] Cluster seems broken after pg_basebackup

[Guillaume Drolet]

Hi,

Yesterday I ran a pg_basebackup of my cluster. Since it has completed, my
cluster doesn't work properly. I tried restarting the computer (or service)
a few times but I always get the same messages in my logs (it's in French.
If someone is willing to help me I can try to translate the logs. Just ask):

2015-02-06 07:11:38 EST LOG:  le système de bases de données a été
interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
2015-02-06 07:11:38 EST LOG:  le système de bases de données n'a pas été
arrêté proprement ; restauration
automatique en cours
2015-02-06 07:11:38 EST LOG:  record with zero length at 24B/2C000160
2015-02-06 07:11:38 EST LOG:  la ré-exécution n'est pas nécessaire
2015-02-06 07:11:38 EST LOG:  le système de bases de données est prêt pour
accepter les connexions
2015-02-06 07:11:38 EST LOG:  lancement du processus autovacuum
2015-02-06 07:11:38 EST FATAL:  le rôle « 208375PT$ » n'existe pas

Then if I start pgAdmin I get a series of pop-ups I have to click OK to to
continue:

An error has ocurred: Column not found in pgSet: "datlastsysoid"
An error has ocurred: Column not found in pgSet: datlastsysoid
An error has ocurred: Column not found in pgSet: oid
An error has ocurred: Column not found in pgSet: encoding
An error has ocurred: Column not found in pgSet: Connection to database
broken

And after that, I went back to the log file and there's new information
added:

2015-02-06 07:51:05 EST LOG:  processus serveur (PID 184) a été arrêté par
l'exception 0x8004
2015-02-06 07:51:05 EST DÉTAIL:  Le processus qui a échoué exécutait :
SELECT version();
2015-02-06 07:51:05 EST ASTUCE :  Voir le fichier d'en-tête C « ntstatus.h
» pour une description de la valeur
hexadécimale.
2015-02-06 07:51:05 EST LOG:  arrêt des autres processus serveur actifs
2015-02-06 07:51:05 EST ATTENTION:  arrêt de la connexion à cause de
l'arrêt brutal d'un autre processus serveur
2015-02-06 07:51:05 EST DÉTAIL:  Le postmaster a commandé à ce processus
serveur d'annuler la transaction
courante et de quitter car un autre processus serveur a quitté
anormalement
et qu'il existe probablement de la mémoire partagée corrompue.
2015-02-06 07:51:05 EST ASTUCE :  Dans un moment, vous devriez être capable
de vous reconnecter à la base de
données et de relancer votre commande.
2015-02-06 07:51:05 EST LOG:  processus d'archivage (PID 692) quitte avec
le code de sortie 1
2015-02-06 07:51:05 EST LOG:  tous les processus serveur se sont arrêtés,
réinitialisation
2015-02-06 07:51:15 EST FATAL:  le bloc de mémoire partagé pré-existant est
toujours en cours d'utilisation
2015-02-06 07:51:15 EST ASTUCE :  Vérifier s'il n'y a pas de vieux
processus serveur en cours d'exécution. Si c'est le
cas, fermez-les.

I was about to try restarting postgresql using the base backup I made
yesterday but since this means I'll have to copy my database again (700 GB
takes a while...) I am looking for a better solution from more experienced
people.

Thanks a lot for helping! Guillaume

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Adrian Klaver]

On 04/18/2014 06:25 AM, Guillaume Drolet wrote:

Thanks for the doc on pg_resetxlog. Will try it next week when I'm
back from Easter holiday, first backuping $PGDATA.

Most of my databases are stored in a default tablespace I defined on a
different disk than that of my PG installation (and $PGDATA): could I
just reinstall PG and then re-link it with my databases on that disk?



Not really, $PGDATA holds the global information for the cluster. If you 
reinstalled which implies another initdb then you would have a 
head($PGDATA) that knows nothing about the rest of the body.


--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Guillaume Drolet]

No the new test cluster isn't running anymore: I deleted it right
after I deleted my main one.

Re the error messages: I was calling pg_ctl with the option -D


2014-04-18 8:44 GMT-04:00 droletguillaume :
>
>
>
> Sent from Samsung Mobile
>
>
>  Original message 
> From: Adrian Klaver
> Date:04-17-2014 20:36 (GMT-05:00)
> To: Guillaume Drolet ,pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Cluster recovery - FATAL: database files are
> incompatible with server. Different PG_CONTROL_VERSION for cluster and
> server.
>
> On 04/17/2014 01:29 PM, Guillaume Drolet wrote:
>> Dear list users,
>>
>> For some tests, I installed a new cluster with different parameters
>> than the ones I had used a while ago to create the cluster I use for
>> my day-to-day activities (let's call it my main cluster). I used
>> initdb --no-locale -E UTF8 -D . Then I used pg_ctl -D
>>  to start my new cluster. So far so good.
>>
>> Then, I realized that I wanted to add a database superuser with the
>> option -U to initdb so I decided to delete the newly created cluster.
>> Instead, I mistakenly deleted the data directory of my main cluster
>> (doh!), using Shift-Delete. I recovered the deleted data directory
>> using Panda Recovery Tool and copied it back into its location (the
>> location pointed to by $PGDATA).
>>
>> Now for the real problems: if I go in pgAdmin and try to connect to my
>> cluster, I get the "server not listening" message. I also tried going
>> to the Services and restarting postgresql but it says it is already
>> stopped and if I try to start it, I get a message that it has started
>> but then stopped because it was unused.
>>
>> If I try pg_ctl start, I get this message (my translation from
>> French):  FATAL: database files are incompatible with server. DETAIL:
>> Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
>> while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
>> Looks like you need initdb.
>
>
> More thoughts.
>
> What happened to the new cluster?
>
> Is it still running?
>
> When you got the error messages where you pointing at the old or new
> cluster?
>
>>
>> I really don't know what to do and would appreciate any help, if
>> anything can be done to recover my databases. My PG version is 9.3.3
>> on a Windows 7 64-bit OS.
>>
>> Best regards and many thanks for your advice,
>>
>> Guillaume
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@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

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Guillaume Drolet]

Thanks for the doc on pg_resetxlog. Will try it next week when I'm
back from Easter holiday, first backuping $PGDATA.

Most of my databases are stored in a default tablespace I defined on a
different disk than that of my PG installation (and $PGDATA): could I
just reinstall PG and then re-link it with my databases on that disk?

2014-04-17 20:29 GMT-04:00 Adrian Klaver :
> On 04/17/2014 01:29 PM, Guillaume Drolet wrote:
>>
>> Dear list users,
>>
>> For some tests, I installed a new cluster with different parameters
>> than the ones I had used a while ago to create the cluster I use for
>> my day-to-day activities (let's call it my main cluster). I used
>> initdb --no-locale -E UTF8 -D . Then I used pg_ctl -D
>>  to start my new cluster. So far so good.
>>
>> Then, I realized that I wanted to add a database superuser with the
>> option -U to initdb so I decided to delete the newly created cluster.
>> Instead, I mistakenly deleted the data directory of my main cluster
>> (doh!), using Shift-Delete. I recovered the deleted data directory
>> using Panda Recovery Tool and copied it back into its location (the
>> location pointed to by $PGDATA).
>>
>> Now for the real problems: if I go in pgAdmin and try to connect to my
>> cluster, I get the "server not listening" message. I also tried going
>> to the Services and restarting postgresql but it says it is already
>> stopped and if I try to start it, I get a message that it has started
>> but then stopped because it was unused.
>>
>> If I try pg_ctl start, I get this message (my translation from
>> French):  FATAL: database files are incompatible with server. DETAIL:
>> Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
>> while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
>> Looks like you need initdb.
>>
>> I really don't know what to do and would appreciate any help, if
>> anything can be done to recover my databases. My PG version is 9.3.3
>> on a Windows 7 64-bit OS.
>
>
> Further investigation found pg_resetxlog:
>
> http://www.postgresql.org/docs/9.3/static/app-pgresetxlog.html
>
> I have never used it, so all I can do is point to the docs. I would say that
> if you go this route make a copy of your $PGDATA in another location for
> safe keeping in the event things go wrong.
>
>
>
>>
>> Best regards and many thanks for your advice,
>>
>> Guillaume
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@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

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Adrian Klaver]

On 04/17/2014 01:29 PM, Guillaume Drolet wrote:

Dear list users,

For some tests, I installed a new cluster with different parameters
than the ones I had used a while ago to create the cluster I use for
my day-to-day activities (let's call it my main cluster). I used
initdb --no-locale -E UTF8 -D . Then I used pg_ctl -D
 to start my new cluster. So far so good.

Then, I realized that I wanted to add a database superuser with the
option -U to initdb so I decided to delete the newly created cluster.
Instead, I mistakenly deleted the data directory of my main cluster
(doh!), using Shift-Delete. I recovered the deleted data directory
using Panda Recovery Tool and copied it back into its location (the
location pointed to by $PGDATA).

Now for the real problems: if I go in pgAdmin and try to connect to my
cluster, I get the "server not listening" message. I also tried going
to the Services and restarting postgresql but it says it is already
stopped and if I try to start it, I get a message that it has started
but then stopped because it was unused.

If I try pg_ctl start, I get this message (my translation from
French):  FATAL: database files are incompatible with server. DETAIL:
Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
Looks like you need initdb.



More thoughts.

What happened to the new cluster?

Is it still running?

When you got the error messages where you pointing at the old or new 
cluster?




I really don't know what to do and would appreciate any help, if
anything can be done to recover my databases. My PG version is 9.3.3
on a Windows 7 64-bit OS.

Best regards and many thanks for your advice,

Guillaume





--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Adrian Klaver]

On 04/17/2014 01:29 PM, Guillaume Drolet wrote:

Dear list users,

For some tests, I installed a new cluster with different parameters
than the ones I had used a while ago to create the cluster I use for
my day-to-day activities (let's call it my main cluster). I used
initdb --no-locale -E UTF8 -D . Then I used pg_ctl -D
 to start my new cluster. So far so good.

Then, I realized that I wanted to add a database superuser with the
option -U to initdb so I decided to delete the newly created cluster.
Instead, I mistakenly deleted the data directory of my main cluster
(doh!), using Shift-Delete. I recovered the deleted data directory
using Panda Recovery Tool and copied it back into its location (the
location pointed to by $PGDATA).

Now for the real problems: if I go in pgAdmin and try to connect to my
cluster, I get the "server not listening" message. I also tried going
to the Services and restarting postgresql but it says it is already
stopped and if I try to start it, I get a message that it has started
but then stopped because it was unused.

If I try pg_ctl start, I get this message (my translation from
French):  FATAL: database files are incompatible with server. DETAIL:
Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
Looks like you need initdb.

I really don't know what to do and would appreciate any help, if
anything can be done to recover my databases. My PG version is 9.3.3
on a Windows 7 64-bit OS.


Further investigation found pg_resetxlog:

http://www.postgresql.org/docs/9.3/static/app-pgresetxlog.html

I have never used it, so all I can do is point to the docs. I would say 
that if you go this route make a copy of your $PGDATA in another 
location for safe keeping in the event things go wrong.





Best regards and many thanks for your advice,

Guillaume





--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Adrian Klaver]

On 04/17/2014 01:29 PM, Guillaume Drolet wrote:

Dear list users,

For some tests, I installed a new cluster with different parameters
than the ones I had used a while ago to create the cluster I use for
my day-to-day activities (let's call it my main cluster). I used
initdb --no-locale -E UTF8 -D . Then I used pg_ctl -D
 to start my new cluster. So far so good.

Then, I realized that I wanted to add a database superuser with the
option -U to initdb so I decided to delete the newly created cluster.


You do not use initdb to add superusers, that can be done with 
createuser from the command line or CREATE ROLE via SQL. The -U option 
just establishes the first superuser and is not even necessary as it 
defaults to the system user running initdb.



Instead, I mistakenly deleted the data directory of my main cluster
(doh!), using Shift-Delete. I recovered the deleted data directory
using Panda Recovery Tool and copied it back into its location (the
location pointed to by $PGDATA).


Are you sure you recovered the entire directory?



Now for the real problems: if I go in pgAdmin and try to connect to my
cluster, I get the "server not listening" message. I also tried going
to the Services and restarting postgresql but it says it is already
stopped and if I try to start it, I get a message that it has started
but then stopped because it was unused.

If I try pg_ctl start, I get this message (my translation from
French):  FATAL: database files are incompatible with server. DETAIL:
Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
Looks like you need initdb.


Well 937 is the version for 9.3. The version number increases with time, 
but I am pretty sure Postgres is no where near 16795209, so this does 
not look promising :( At a guess either the delete happened while the 
cluster was running and a file or files where corrupted or the data 
recovery was not entirely successful.




I really don't know what to do and would appreciate any help, if
anything can be done to recover my databases. My PG version is 9.3.3
on a Windows 7 64-bit OS.


Do you have a backup of your main cluster?



Best regards and many thanks for your advice,

Guillaume





--
Adrian Klaver
adrian.kla...@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

[GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Guillaume Drolet]

Dear list users,

For some tests, I installed a new cluster with different parameters
than the ones I had used a while ago to create the cluster I use for
my day-to-day activities (let's call it my main cluster). I used
initdb --no-locale -E UTF8 -D . Then I used pg_ctl -D
 to start my new cluster. So far so good.

Then, I realized that I wanted to add a database superuser with the
option -U to initdb so I decided to delete the newly created cluster.
Instead, I mistakenly deleted the data directory of my main cluster
(doh!), using Shift-Delete. I recovered the deleted data directory
using Panda Recovery Tool and copied it back into its location (the
location pointed to by $PGDATA).

Now for the real problems: if I go in pgAdmin and try to connect to my
cluster, I get the "server not listening" message. I also tried going
to the Services and restarting postgresql but it says it is already
stopped and if I try to start it, I get a message that it has started
but then stopped because it was unused.

If I try pg_ctl start, I get this message (my translation from
French):  FATAL: database files are incompatible with server. DETAIL:
Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
Looks like you need initdb.

I really don't know what to do and would appreciate any help, if
anything can be done to recover my databases. My PG version is 9.3.3
on a Windows 7 64-bit OS.

Best regards and many thanks for your advice,

Guillaume


-- 
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] CLUSTER, REINDEX and VACUUM on batch ops

[Sergey Konoplev]

On Wed, Apr 24, 2013 at 8:49 AM, François Beausoleil
 wrote:
> Presently, I'm migrating each partition individually to add NOT NULL, set a 
> default value and update the table to have correct values. Essentially, I'm 
> doing this:
>
> ALTER TABLE parent ADD COLUMN new_field int; -- adds the field to all child 
> tables - runs quickly
>
> -- the bulk of the data transfer
> for each partition in partitions:
>   BEGIN;
>   UPDATE partition SET new_field = 0;
>   ALTER TABLE partition
>   ALTER COLUMN new_field SET NOT NULL
> , ALTER COLUMN new_field SET DEFAULT 0;
>   COMMIT;
>
>   CLUSTER partition USING partition_pkey;
>   REINDEX TABLE partition;
>   VACUUM ANALYZE partition;
> done
>
> After I've clustered the table, must I reindex and vacuum as well? It is 
> unclear to me if clustering a table reindexes or not: the docs at 
> http://www.postgresql.org/docs/current/static/sql-cluster.html are silent on 
> the matter, but do mention that an ANALYZE is in order.

CLUSTER does full table rewrite including all its indexes so REINDEX
is not required after it. It is mentioned in the docs implicitly:

<<
When an index scan is used, a temporary copy of the table is created
that contains the table data in the index order. Temporary copies of
each index on the table are created as well. Therefore, you need free
space on disk at least equal to the sum of the table size and the
index sizes.

When a sequential scan and sort is used, a temporary sort file is also
created, so that the peak temporary space requirement is as much as
double the table size, plus the index sizes.
>>

BTW, you do not need to do the ALTERs for each partition. What you
need to do is:

1. add the column to the parent,
2. set the default constraint on the column of the parent,
3. update the column in partitions to the value,
4. set the not null constraint on the parent.

It will be better from the point of view of inheritance as the new
column will be fully inherited from the parent rather then partially
overloaded in partitions.

--
Kind regards,
Sergey Konoplev
Database and Software Consultant

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-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] CLUSTER, REINDEX and VACUUM on batch ops

[François Beausoleil]

Hi all!

I have a partitioned table with millions of rows per weekly partition. I am 
adding new fields, with null values and no default values to ensure I had a 
reasonable migration time. All downstream code knows how to work with null 
fields.

Presently, I'm migrating each partition individually to add NOT NULL, set a 
default value and update the table to have correct values. Essentially, I'm 
doing this:

ALTER TABLE parent ADD COLUMN new_field int; -- adds the field to all child 
tables - runs quickly

-- the bulk of the data transfer
for each partition in partitions:
  BEGIN;
  UPDATE partition SET new_field = 0;
  ALTER TABLE partition
  ALTER COLUMN new_field SET NOT NULL
, ALTER COLUMN new_field SET DEFAULT 0;
  COMMIT;

  CLUSTER partition USING partition_pkey;
  REINDEX TABLE partition;
  VACUUM ANALYZE partition;
done

After I've clustered the table, must I reindex and vacuum as well? It is 
unclear to me if clustering a table reindexes or not: the docs at 
http://www.postgresql.org/docs/current/static/sql-cluster.html are silent on 
the matter, but do mention that an ANALYZE is in order.

Thanks!
François Beausoleil

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Cluster table and order information

[Dario Beraldi]

Quoting Andy Colson :


On 2/1/2011 9:08 AM, Dario Beraldi wrote:

Hello,

From the documentation of CLUSTER table
(http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I
understand that clustering can be achieved by re-creating the table like
this:

CREATE TABLE newtable AS
SELECT * FROM table ORDER BY columnlist;

My question is: If I upload with COPY a datafile which is already
correctly sorted, can I inform postgres of such order, so that no
clustering is necessary after the import? In other words, how can I tell
postgres that my file is order by this and that column?

Many thanks!

Dario



The planner has no knowledge of cluster.  Meaning PG will query a  
clustered and unclustered table exactly the same way.  A table is  
not marked or anything as clustered.  And in fact, during usage of a  
table it'll become unclustered.


Clustering is only useful when you are going to read multiple  
records in the same order as an index.   It turns "more random  
seeks" into "more sequential reads".


If your COPY loads data in indexed order, then just dont run the cluster.

-Andy


Thanks very much Andy, this clarifies my doubts.

I was misled by the docs saying "When a table is clustered, PostgreSQL  
remembers which index it was clustered by" which made me think that  
the order information is stored somewhere.


All the best
Dario

--
The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.



--
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] Cluster table and order information

[Andy Colson]

On 2/1/2011 10:17 AM, Dario Beraldi wrote:

Quoting Andy Colson :


On 2/1/2011 9:08 AM, Dario Beraldi wrote:

Hello,

From the documentation of CLUSTER table
(http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I
understand that clustering can be achieved by re-creating the table like
this:

CREATE TABLE newtable AS
SELECT * FROM table ORDER BY columnlist;

My question is: If I upload with COPY a datafile which is already
correctly sorted, can I inform postgres of such order, so that no
clustering is necessary after the import? In other words, how can I tell
postgres that my file is order by this and that column?

Many thanks!

Dario



The planner has no knowledge of cluster. Meaning PG will query a
clustered and unclustered table exactly the same way. A table is not
marked or anything as clustered. And in fact, during usage of a table
it'll become unclustered.

Clustering is only useful when you are going to read multiple records
in the same order as an index. It turns "more random seeks" into "more
sequential reads".

If your COPY loads data in indexed order, then just dont run the cluster.

-Andy


Thanks very much Andy, this clarifies my doubts.

I was misled by the docs saying "When a table is clustered, PostgreSQL
remembers which index it was clustered by" which made me think that the
order information is stored somewhere.

All the best
Dario



The next sentience clears it up:

The form "CLUSTER table_name" reclusters the table using the same index 
as before.



-Andy

--
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] Cluster table and order information

[Andy Colson]

On 2/1/2011 9:08 AM, Dario Beraldi wrote:

Hello,

 From the documentation of CLUSTER table
(http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I
understand that clustering can be achieved by re-creating the table like
this:

CREATE TABLE newtable AS
SELECT * FROM table ORDER BY columnlist;

My question is: If I upload with COPY a datafile which is already
correctly sorted, can I inform postgres of such order, so that no
clustering is necessary after the import? In other words, how can I tell
postgres that my file is order by this and that column?

Many thanks!

Dario



The planner has no knowledge of cluster.  Meaning PG will query a 
clustered and unclustered table exactly the same way.  A table is not 
marked or anything as clustered.  And in fact, during usage of a table 
it'll become unclustered.


Clustering is only useful when you are going to read multiple records in 
the same order as an index.   It turns "more random seeks" into "more 
sequential reads".


If your COPY loads data in indexed order, then just dont run the cluster.

-Andy

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

[GENERAL] Cluster table and order information

[Dario Beraldi]

Hello,

From the documentation of CLUSTER table  
(http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I  
understand that clustering can be achieved by re-creating the table  
like this:


CREATE TABLE newtable AS
SELECT * FROM table ORDER BY columnlist;

My question is: If I upload with COPY a datafile which is already  
correctly sorted, can I inform postgres of such order, so that no  
clustering is necessary after the import? In other words, how can I  
tell postgres that my file is order by this and that column?


Many thanks!

Dario

--

Dr. Dario Beraldi
Institute of Evolutionary Biology
University of Edinburgh
West Mains Road
Edinburgh EH9 3JT
Scotland, UK


--
The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.



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

[GENERAL] Cluster with LATIN1 and UTF-8

[paulo matadr]

Hi all,
Im try install postgres 9.0  on rhel
and I try install cluster with 2 options of databases  LATIN1 and UTF-8.
It's possible?



 


Paulo


  

Re: [GENERAL] CLUSTER cannot complete

[Greg Smith]

Vick Khera wrote:

On Mon, Feb 15, 2010 at 9:54 AM, Marcin Krol  wrote:
  

I didn't add or delete many rows from that table. Quite a lot of UPDATEs are
running daily, though.



Every update is equal to insert + delete, except in certain
circumstances in 8.3 and above.
  


Well, it's still an insert + delete of the actual data row itself even 
in 8.3, the later versions just optimize how that impacts the index 
related changes there better.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us

Re: [GENERAL] CLUSTER cannot complete

[Vick Khera]

On Mon, Feb 15, 2010 at 9:54 AM, Marcin Krol  wrote:
> I didn't add or delete many rows from that table. Quite a lot of UPDATEs are
> running daily, though.

Every update is equal to insert + delete, except in certain
circumstances in 8.3 and above.

-- 
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] CLUSTER on 2 indexes ?

[Scott Marlowe]

On Mon, Feb 15, 2010 at 9:47 AM, Joao Ferreira gmail
 wrote:
> Hello,
>
> Considering the CLUSTER operation on a frequently updated table, if I
> have 2 indexes on the table how do I choose one of them ? or is it
> possible to have CLUSTER take both into consideration...

You could have a multi-column index

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

[GENERAL] CLUSTER on 2 indexes ?

[Joao Ferreira gmail]

Hello,

Considering the CLUSTER operation on a frequently updated table, if I
have 2 indexes on the table how do I choose one of them ? or is it
possible to have CLUSTER take both into consideration...

my table is read from based on two columns: a 'timestamp' integer column
(actually a UTC integer timestamp) and a text columun called 'var' that
separates raw data from each other... something like a 'product type' in
a wharehouse example.

All queries that read from the table specify a time range and also the
'product type'. I have indexes on both.

Evidently no CLUSTERing should be worse than clustering on any of them,
but is there a way to have the two or choose the most profitable ?

thanks
Joao



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

[GENERAL] CLUSTER cannot complete

[Marcin Krol]

Hello everyone,

Resolved, I've done:

hrs=# CLUSTER hosts_ip_idx ON hosts;
CLUSTER
hrs=#
hrs=#
hrs=# SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class 
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 
'public') ORDER BY size_in_bytes DESC LIMIT 10;

 size_in_bytes |   relname
---+--
548864 | reservation
376832 | hosts
106496 | reservation_hosts
 49152 | reservation_businessneed_idx
 49152 | hosts_ip_idx
 40960 | reservation_status_idx
 40960 | reservation_hosts_reservation_id_idx
 40960 | reservation_hosts_host_id_idx
 40960 | hosts_hostname_idx
 40960 | hosts_location_idx
(10 rows)


Why such a dramatic reduction in table size -- from 80MB to 0.55MB?!

I didn't add or delete many rows from that table. Quite a lot of UPDATEs 
are running daily, though.


Regards,
mk




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

[GENERAL] CLUSTER cannot complete

[Marcin Krol]

Hello everyone,

I got this, first CLUSTER was hanging forever, I cancelled after half an 
hour:


hrs=# CLUSTER hosts;
Cancel request sent
ERROR:  canceling statement due to user request



Then I restarted postgres and get this now:

hrs=# CLUSTER hosts;
ERROR:  there is no previously clustered index for table "hosts"

Regards,
mk





--
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] cluster replication over only http protocol

[Sam Mason]

On Wed, Aug 26, 2009 at 01:13:48AM +0200, Szabolcs MMMrton wrote:
> my situation is:
> - i have a running/working postgre databse
> - i have to make another database exactly the same as the first (for
> development purposes)
> - the two databases have only(!) http or https connection, nothing else !
> 
> is there a method to set-up a cluster (replication) using only http
> protocols?

Are these two databases on different boxes?  Not sure how much it
matters either way, just write a simple CGI script that does a pg_dump
of the master database.  Have the other box download this and send it
through to its database to be synchronized every few hours.  Is that
enough for development purposes?  You could even pipe it through some
crypto code if you're worried about the data going missing.

-- 
  Sam  http://samason.me.uk/

-- 
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] cluster replication over only http protocol

[Scott Marlowe]

2009/8/25 Szabolcs Márton :
> Hi,
>
>
> somebody could give me an advice what should i look for?
>
> my situation is:
> - i have a running/working postgre databse
> - i have to make another database exactly the same as the first (for
> development purposes)
> - the two databases have only(!) http or https connection, nothing else !

How does limiting access to http make this more secure / better?

Here's what i'd do.  Shut down apache, startup sshd on port 80, use tunneling.

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

[GENERAL] cluster replication over only http protocol

[Szabolcs Márton]

Hi,


somebody could give me an advice what should i look for?

my situation is:
- i have a running/working postgre databse
- i have to make another database exactly the same as the first (for
development purposes)
- the two databases have only(!) http or https connection, nothing else !

is there a method to set-up a cluster (replication) using only http
protocols?
(any other ports are closed, and filtered by firewalls)

they should be transaction safe as weel, very very little replication time.

is any feature in postgre support this, or any 3rd party tool ?
what kind of thing should i look for?

thanks in advance,
Szabi

Re: [GENERAL] Cluster Up-time.

[Michael Fuhr]

On Mon, Aug 18, 2008 at 03:53:32AM -0700, Alexi Gen wrote:
> Is there a table/view available from where I can check what time the cluster 
> was started?
> Need this to calculate the uptime of the cluster.

In PostgreSQL 8.1 and later you can run these queries to get the start
time and uptime:

SELECT pg_postmaster_start_time();
SELECT now() - pg_postmaster_start_time();

-- 
Michael Fuhr

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

[GENERAL] Cluster Up-time.

[Alexi Gen]

Hello,

Is there a table/view available from where I can check what time the cluster 
was started?
Need this to calculate the uptime of the cluster.
Or is there something else that I need to do in order to calculate this?
Any help on this is appreciated.

Cheers!
Alexi



Send instant messages to your online friends http://uk.messenger.yahoo.com 

Re: [GENERAL] Cluster table

[Tom Lane]

"Kopljan Michael" <[EMAIL PROTECTED]> writes:
> Table is physically ordered by primary key. I try to cluster table with : 
> CLUSTER cluster_idx_dok" ON "ITerp"."Dok" ;
> but Table is still physically ordered by primary key.

What's your evidence for saying so?

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] Cluster table

[Kopljan Michael]

I'm have this table :

 

CREATE TABLE "ITerp"."Dok" (

  "id_dok" INTEGER DEFAULT "AutoIncGet"('Dok'::text) NOT NULL, 

  "sifvrste" CHAR(3) NOT NULL, 

  "id_objekat1" INTEGER DEFAULT 0, 

  "id_objekat2" INTEGER DEFAULT 0, 

  "id_partner" INTEGER DEFAULT 0, 

  "broj" INTEGER NOT NULL, 

  "ext" VARCHAR(15), 

  "datum" DATE DEFAULT date(now()) NOT NULL, 

  "valuta" DATE DEFAULT date(now()), 

  "rabat" NUMERIC(10,2), 

  "opis" VARCHAR(60), 

  "napomena" VARCHAR(200), 

  "sumiznos1" NUMERIC(18,2) DEFAULT 0, 

  "sumporez1" NUMERIC(14,2) DEFAULT 0, 

  "sumporez2" NUMERIC(14,2) DEFAULT 0, 

  "sumiznos2" NUMERIC(18,2) DEFAULT 0, 

  "sumiznos3" NUMERIC(18,2) DEFAULT 0, 

  "id_komitent" INTEGER NOT NULL, 

  "radnik" VARCHAR(40), 

  "vezadok" INTEGER DEFAULT 0, 

  "vpkkljuc" BOOLEAN DEFAULT false, 

  "vpkiznos" NUMERIC(18,2) DEFAULT 0.00, 

  "vpktrosak" NUMERIC(18,2) DEFAULT 0.00, 

  "status" SMALLINT DEFAULT 0, 

  "zapisnik" VARCHAR(140) DEFAULT ''::character varying, 

  "fakbroj" VARCHAR(15), 

  "vpktip" CHAR(1) DEFAULT ''::bpchar, 

  "sumiznos4" NUMERIC(18,2) DEFAULT 0.00, 

  "uplataiznos" NUMERIC(18,2) DEFAULT 0.00, 

  "uplatadatum" DATE DEFAULT date(now()), 

  "kpismoiznos" NUMERIC(18,2) DEFAULT 0.00, 

  "kpismodatum" DATE DEFAULT date(now()), 

  "datumpdv" DATE DEFAULT date(now()), 

  "altvalutaiznos" NUMERIC(14,4) DEFAULT 0.00, 

  "altvalutamon" VARCHAR(5) DEFAULT ''::bpchar, 

  "vpkcarina" BOOLEAN DEFAULT false, 

  "tipracuna" INTEGER DEFAULT 1, 

  "cenaext" NUMERIC(14,4) DEFAULT 0.000, 

  "pecat_i" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(), 

  "pecat_u" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(), 

  "idtoken" NUMERIC(14,4), 

  "id_storno" INTEGER DEFAULT 0, 

  CONSTRAINT "Dok_pkey" PRIMARY KEY("id_dok"), 

  CONSTRAINT "fk_komitent" FOREIGN KEY ("id_komitent")

REFERENCES "ITerp"."Komitent"("id_komitent")

ON DELETE RESTRICT

ON UPDATE RESTRICT

NOT DEFERRABLE, 

  CONSTRAINT "fk_vrsta" FOREIGN KEY ("sifvrste")

REFERENCES "ITerp"."VrstaDok"("sifvrsta")

ON DELETE RESTRICT

ON UPDATE RESTRICT

NOT DEFERRABLE

) WITHOUT OIDS;

 

 

CREATE UNIQUE INDEX "broj_idx_D" ON "ITerp"."Dok"

  USING btree ("sifvrste", "broj", "id_komitent", "id_objekat1");

 

CREATE INDEX "cluster_idx_dok" ON "ITerp"."Dok"

  USING btree ("id_komitent", "sifvrste", "datum", "broj", "id_dok");

 

CREATE INDEX "datum_idx_Dok" ON "ITerp"."Dok"

  USING btree ("datum");

 

CREATE UNIQUE INDEX "fakbroj_idx" ON "ITerp"."Dok"

  USING btree ("id_komitent", "sifvrste", (btrim((fakbroj)::text)))

  WHERE (sifvrste = 'OTP'::bpchar) OR (sifvrste = 'FAK'::bpchar)) OR
(sifvrste = 'PFK'::bpchar)) OR (sifvrste = 'AVU'::bpchar)) OR (sifvrste =
'AVS'::bpchar));

 

CREATE INDEX "idtoken_idx" ON "ITerp"."Dok"

  USING btree ("idtoken");

 

CREATE INDEX "komitentD_idx" ON "ITerp"."Dok"

  USING btree ("id_komitent");

 

CREATE INDEX "objekat1D_idx" ON "ITerp"."Dok"

  USING btree ("id_objekat1");

 

CREATE INDEX "objekat2D_idx" ON "ITerp"."Dok"

  USING btree ("id_objekat2");

 

CREATE INDEX "partnerD_idx" ON "ITerp"."Dok"

  USING btree ("id_partner");

 

CREATE INDEX "pecat_i_idx_dok" ON "ITerp"."Dok"

  USING btree ("pecat_i");

 

CREATE INDEX "sifvrste_idx" ON "ITerp"."Dok"

  USING btree ("sifvrste");

 

CREATE INDEX "vezaD_idx" ON "ITerp"."Dok"

  USING btree ("vezadok");

 

 

Table is physically ordered by primary key. I try to cluster table with : 

 

CLUSTER cluster_idx_dok" ON "ITerp"."Dok" ;

 

but Table is still physically ordered by primary key. Why table is not
reordered by index?

[GENERAL] Cluster table

[Kopljan Michael]

I'm have this table :

 

CREATE TABLE "ITerp"."Dok" (

  "id_dok" INTEGER DEFAULT "AutoIncGet"('Dok'::text) NOT NULL, 

  "sifvrste" CHAR(3) NOT NULL, 

  "id_objekat1" INTEGER DEFAULT 0, 

  "id_objekat2" INTEGER DEFAULT 0, 

  "id_partner" INTEGER DEFAULT 0, 

  "broj" INTEGER NOT NULL, 

  "ext" VARCHAR(15), 

  "datum" DATE DEFAULT date(now()) NOT NULL, 

  "valuta" DATE DEFAULT date(now()), 

  "rabat" NUMERIC(10,2), 

  "opis" VARCHAR(60), 

  "napomena" VARCHAR(200), 

  "sumiznos1" NUMERIC(18,2) DEFAULT 0, 

  "sumporez1" NUMERIC(14,2) DEFAULT 0, 

  "sumporez2" NUMERIC(14,2) DEFAULT 0, 

  "sumiznos2" NUMERIC(18,2) DEFAULT 0, 

  "sumiznos3" NUMERIC(18,2) DEFAULT 0, 

  "id_komitent" INTEGER NOT NULL, 

  "radnik" VARCHAR(40), 

  "vezadok" INTEGER DEFAULT 0, 

  "vpkkljuc" BOOLEAN DEFAULT false, 

  "vpkiznos" NUMERIC(18,2) DEFAULT 0.00, 

  "vpktrosak" NUMERIC(18,2) DEFAULT 0.00, 

  "status" SMALLINT DEFAULT 0, 

  "zapisnik" VARCHAR(140) DEFAULT ''::character varying, 

  "fakbroj" VARCHAR(15), 

  "vpktip" CHAR(1) DEFAULT ''::bpchar, 

  "sumiznos4" NUMERIC(18,2) DEFAULT 0.00, 

  "uplataiznos" NUMERIC(18,2) DEFAULT 0.00, 

  "uplatadatum" DATE DEFAULT date(now()), 

  "kpismoiznos" NUMERIC(18,2) DEFAULT 0.00, 

  "kpismodatum" DATE DEFAULT date(now()), 

  "datumpdv" DATE DEFAULT date(now()), 

  "altvalutaiznos" NUMERIC(14,4) DEFAULT 0.00, 

  "altvalutamon" VARCHAR(5) DEFAULT ''::bpchar, 

  "vpkcarina" BOOLEAN DEFAULT false, 

  "tipracuna" INTEGER DEFAULT 1, 

  "cenaext" NUMERIC(14,4) DEFAULT 0.000, 

  "pecat_i" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(), 

  "pecat_u" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(), 

  "idtoken" NUMERIC(14,4), 

  "id_storno" INTEGER DEFAULT 0, 

  CONSTRAINT "Dok_pkey" PRIMARY KEY("id_dok"), 

  CONSTRAINT "fk_komitent" FOREIGN KEY ("id_komitent")

REFERENCES "ITerp"."Komitent"("id_komitent")

ON DELETE RESTRICT

ON UPDATE RESTRICT

NOT DEFERRABLE, 

  CONSTRAINT "fk_vrsta" FOREIGN KEY ("sifvrste")

REFERENCES "ITerp"."VrstaDok"("sifvrsta")

ON DELETE RESTRICT

ON UPDATE RESTRICT

NOT DEFERRABLE

) WITHOUT OIDS;

 

 

CREATE UNIQUE INDEX "broj_idx_D" ON "ITerp"."Dok"

  USING btree ("sifvrste", "broj", "id_komitent", "id_objekat1");

 

CREATE INDEX "cluster_idx_dok" ON "ITerp"."Dok"

  USING btree ("id_komitent", "sifvrste", "datum", "broj", "id_dok");

 

CREATE INDEX "datum_idx_Dok" ON "ITerp"."Dok"

  USING btree ("datum");

 

CREATE UNIQUE INDEX "fakbroj_idx" ON "ITerp"."Dok"

  USING btree ("id_komitent", "sifvrste", (btrim((fakbroj)::text)))

  WHERE (sifvrste = 'OTP'::bpchar) OR (sifvrste = 'FAK'::bpchar)) OR
(sifvrste = 'PFK'::bpchar)) OR (sifvrste = 'AVU'::bpchar)) OR (sifvrste =
'AVS'::bpchar));

 

CREATE INDEX "idtoken_idx" ON "ITerp"."Dok"

  USING btree ("idtoken");

 

CREATE INDEX "komitentD_idx" ON "ITerp"."Dok"

  USING btree ("id_komitent");

 

CREATE INDEX "objekat1D_idx" ON "ITerp"."Dok"

  USING btree ("id_objekat1");

 

CREATE INDEX "objekat2D_idx" ON "ITerp"."Dok"

  USING btree ("id_objekat2");

 

CREATE INDEX "partnerD_idx" ON "ITerp"."Dok"

  USING btree ("id_partner");

 

CREATE INDEX "pecat_i_idx_dok" ON "ITerp"."Dok"

  USING btree ("pecat_i");

 

CREATE INDEX "sifvrste_idx" ON "ITerp"."Dok"

  USING btree ("sifvrste");

 

CREATE INDEX "vezaD_idx" ON "ITerp"."Dok"

  USING btree ("vezadok");

 

 

Table is physically ordered by primary key. I try to cluster table with : 

 

CLUSTER cluster_idx_dok" ON "ITerp"."Dok" ;

 

but Table is still physically ordered by primary key. Why table is not
reordered by index?

Re: [GENERAL] Cluster using tablespaces?

[Rainer Bauer]

Alvaro Herrera wrote:

> Alvaro Herrera wrote:
>Probably most of the time is going into creating the new table then.
>
>If you are looking for a short-term solution to your problem, maybe the
>best is to follow the recommendation on CLUSTER ref page:

I've read that section before, but I have lots of foreign key relationships
between the tables.

Thanks Alvaro and Tom, but it seems that I will have to live with that
behaviour, until ...

>No, the ALTER TABLE/CLUSTER ON only defines what index will the table be
>clustered on in the future, but it doesn't cluster it at that time.
>Perhaps it could be improved so that if a table rewrite is going to be
>done anyway for some other reason, then make sure the rewrite uses the
>cluster order.  I think it's far from trivial though.

... this has been tried.

Rainer

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Cluster using tablespaces?

[Alvaro Herrera]

Rainer Bauer wrote:
> Alvaro Herrera wrote:

> >It has been theorized that cluster would be faster in general if instead
> >of doing an indexscan we would instead use a seqscan + sort step.  It
> >would be good to measure it.
> 
> Could a reindex on the clustered index speed up the clustering (when executed
> immediatelly before the cluster command)? As I understand it, this index is
> used to fetch the table data in the correct order. Or is most of the time
> spend fetching the table data?

I haven't measured it, but my guess is that most of the time is in
fetching heap pages in random order.

> Also, would it make sense to increase  for the cluster
> operation. This is set to 32MB here on my Windows box as was recommended.

Not sure.  In general yes, but on Windows things are different.


> >> >For btree indexes, there is a temporary copy of the index data, which
> >> >will go wherever you have arranged for temp files to go.  (I think that
> >> >easy user control of this may be new for 8.3, though.)
> >> 
> >> Could you give me a hint where that would be on Windows? I guess this 
> >> might be
> >> worth a try since there are a couple of btree indexes in the database.
> >
> >I think Tom is referring to the new temp_tablespaces config variable.
> 
> I moved the pgsql_tmp directory to another disk, but that didn't speed up the
> cluster command.

Probably most of the time is going into creating the new table then.

If you are looking for a short-term solution to your problem, maybe the
best is to follow the recommendation on CLUSTER ref page:

There is another way to cluster data. The CLUSTER command
reorders the original table by scanning it using the index you
specify. This can be slow on large tables because the rows are
fetched from the table in index order, and if the table is
disordered, the entries are on random pages, so there is one
disk page retrieved for every row moved. (PostgreSQL has a
cache, but the majority of a big table will not fit in the
cache.) The other way to cluster a table is to use

CREATE TABLE newtable AS
SELECT * FROM table ORDER BY columnlist;

which uses the PostgreSQL sorting code to produce the desired
order; this is usually much faster than an index scan for
disordered data. Then you drop the old table, use ALTER TABLE
... RENAME to rename newtable to the old name, and recreate the
table's indexes. The big disadvantage of this approach is that
it does not preserve OIDs, constraints, foreign key
relationships, granted privileges, and other ancillary
properties of the table — all such items must be manually
recreated. Another disadvantage is that this way requires a sort
temporary file about the same size as the table itself, so peak
disk usage is about three times the table size instead of twice
the table size. 

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"La experiencia nos dice que el hombre peló millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarían al hombre" (Ijon Tichy)

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Cluster using tablespaces?

[Rainer Bauer]

Alvaro Herrera wrote:

>Rainer Bauer wrote:
>> Ok, I expected that. Does this work:
>> ALTER TABLE foo CLUSTER ON index_name SET TABLESPACE new_tablespace
>> 
>> I.e. is the table moved to the other tablespace and clustered at the same 
>> time
>> or are these independant operations?
>
>No, the ALTER TABLE/CLUSTER ON only defines what index will the table be
>clustered on in the future, but it doesn't cluster it at that time.
>Perhaps it could be improved so that if a table rewrite is going to be
>done anyway for some other reason, then make sure the rewrite uses the
>cluster order.  I think it's far from trivial though.

Yeah that is what I was originally looking for.

>> What I am trying to achieve is cutting down the time the cluster command
>> takes. I thought the most promising way would be if the new data is written 
>> to
>> different drive.
>
>It has been theorized that cluster would be faster in general if instead
>of doing an indexscan we would instead use a seqscan + sort step.  It
>would be good to measure it.

Could a reindex on the clustered index speed up the clustering (when executed
immediatelly before the cluster command)? As I understand it, this index is
used to fetch the table data in the correct order. Or is most of the time
spend fetching the table data?

Also, would it make sense to increase  for the cluster
operation. This is set to 32MB here on my Windows box as was recommended.

>From my questions you can see that I don't know how the clustering is working
internally. I.e. I don't have a concrete idea how to make cluster any faster.

>> >For btree indexes, there is a temporary copy of the index data, which
>> >will go wherever you have arranged for temp files to go.  (I think that
>> >easy user control of this may be new for 8.3, though.)
>> 
>> Could you give me a hint where that would be on Windows? I guess this might 
>> be
>> worth a try since there are a couple of btree indexes in the database.
>
>I think Tom is referring to the new temp_tablespaces config variable.

I moved the pgsql_tmp directory to another disk, but that didn't speed up the
cluster command.

Rainer

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: Btree indexes temp copy Was [Re: [GENERAL] Cluster using tablespaces?]

[Tom Lane]

Ow Mun Heng <[EMAIL PROTECTED]> writes:
> On Wed, 2007-11-28 at 18:30 -0500, Tom Lane wrote:
>> For btree indexes, there is a temporary copy of the index data, which
>> will go wherever you have arranged for temp files to go.  (I think that
>> easy user control of this may be new for 8.3, though.)

> In 8.2.5 is there a way to control this?

You can replace the pgsql_tmp subdirectory with a symlink to someplace.

This is outside the purview of the database, so for instance it won't
survive a dump/reload, but it can be a workable hack ...

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Btree indexes temp copy Was [Re: [GENERAL] Cluster using tablespaces?]

[Ow Mun Heng]

On Wed, 2007-11-28 at 18:30 -0500, Tom Lane wrote:

> For btree indexes, there is a temporary copy of the index data, which
> will go wherever you have arranged for temp files to go.  (I think that
> easy user control of this may be new for 8.3, though.)

In 8.2.5 is there a way to control this? I noticed that when I create a
new index etc, it will hit the os disk (that's where the main tablespace
is located but no data is actually being stored there except for PG's
template1/0 etc table)

I would rather it hit the Raid Array. I looked at the postgres.conf file
but didn't see anything obvious to point out the temp location..

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Cluster using tablespaces?

[Alvaro Herrera]

Rainer Bauer wrote:
> Tom Lane wrote:
> 
> >Rainer Bauer <[EMAIL PROTECTED]> writes:
> >
> >> "During the cluster operation, a temporary copy of the table is created 
> >> that
> >> contains the table data in the index order. Temporary copies of each index 
> >> on
> >> the table are created as well."
> >
> >That's probably a bit misleading.  There is no "temporary" copy of the
> >table, just the new permanent copy.  The document is trying to point out
> >to you that the transient disk space requirement will be 2X the table
> >size, but maybe we could phrase it better.
> 
> Ok, I expected that. Does this work:
> ALTER TABLE foo CLUSTER ON index_name SET TABLESPACE new_tablespace
> 
> I.e. is the table moved to the other tablespace and clustered at the same time
> or are these independant operations?

No, the ALTER TABLE/CLUSTER ON only defines what index will the table be
clustered on in the future, but it doesn't cluster it at that time.
Perhaps it could be improved so that if a table rewrite is going to be
done anyway for some other reason, then make sure the rewrite uses the
cluster order.  I think it's far from trivial though.

> What I am trying to achieve is cutting down the time the cluster command
> takes. I thought the most promising way would be if the new data is written to
> different drive.

It has been theorized that cluster would be faster in general if instead
of doing an indexscan we would instead use a seqscan + sort step.  It
would be good to measure it.

> >For btree indexes, there is a temporary copy of the index data, which
> >will go wherever you have arranged for temp files to go.  (I think that
> >easy user control of this may be new for 8.3, though.)
> 
> Could you give me a hint where that would be on Windows? I guess this might be
> worth a try since there are a couple of btree indexes in the database.

I think Tom is referring to the new temp_tablespaces config variable.

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"We are who we choose to be", sang the goldfinch
when the sun is high (Sandman)

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Cluster using tablespaces?

[Rainer Bauer]

Tom Lane wrote:

>Rainer Bauer <[EMAIL PROTECTED]> writes:
>
>> "During the cluster operation, a temporary copy of the table is created that
>> contains the table data in the index order. Temporary copies of each index on
>> the table are created as well."
>
>That's probably a bit misleading.  There is no "temporary" copy of the
>table, just the new permanent copy.  The document is trying to point out
>to you that the transient disk space requirement will be 2X the table
>size, but maybe we could phrase it better.

Ok, I expected that. Does this work:
ALTER TABLE foo CLUSTER ON index_name SET TABLESPACE new_tablespace

I.e. is the table moved to the other tablespace and clustered at the same time
or are these independant operations?

What I am trying to achieve is cutting down the time the cluster command
takes. I thought the most promising way would be if the new data is written to
different drive.

>For btree indexes, there is a temporary copy of the index data, which
>will go wherever you have arranged for temp files to go.  (I think that
>easy user control of this may be new for 8.3, though.)

Could you give me a hint where that would be on Windows? I guess this might be
worth a try since there are a couple of btree indexes in the database.

Rainer

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Cluster using tablespaces?

[Tom Lane]

Rainer Bauer <[EMAIL PROTECTED]> writes:
> Erik Jones wrote:
>> What temporary created data are you referring to?

> The one described in the manual
> :

> "During the cluster operation, a temporary copy of the table is created that
> contains the table data in the index order. Temporary copies of each index on
> the table are created as well."

That's probably a bit misleading.  There is no "temporary" copy of the
table, just the new permanent copy.  The document is trying to point out
to you that the transient disk space requirement will be 2X the table
size, but maybe we could phrase it better.

For btree indexes, there is a temporary copy of the index data, which
will go wherever you have arranged for temp files to go.  (I think that
easy user control of this may be new for 8.3, though.)

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Cluster using tablespaces?

[Rainer Bauer]

Erik Jones wrote:

>> is there a way to instruct cluster to store the temporary created  
>> data on a
>> different tablespace (i.e. drive)? If not, wouldn't that have a decent
>> performance impact or is most of the time spend retrieving the data  
>> in index
>> order?
>
>What temporary created data are you referring to?

The one described in the manual
:

"During the cluster operation, a temporary copy of the table is created that
contains the table data in the index order. Temporary copies of each index on
the table are created as well."

Rainer

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Cluster using tablespaces?

[Martin Gainty]

start here
http://www.postgresql.org/docs/8.1/static/creating-cluster.html

M-
- Original Message -
From: "Erik Jones" <[EMAIL PROTECTED]>
To: "Rainer Bauer" <[EMAIL PROTECTED]>
Cc: 
Sent: Wednesday, November 28, 2007 5:59 PM
Subject: Re: [GENERAL] Cluster using tablespaces?



On Nov 28, 2007, at 4:47 PM, Rainer Bauer wrote:

> Hello,
>
> is there a way to instruct cluster to store the temporary created
> data on a
> different tablespace (i.e. drive)? If not, wouldn't that have a decent
> performance impact or is most of the time spend retrieving the data
> in index
> order?
>
> Rainer

What temporary created data are you referring to?  Do you mean the
contents of the the other cluster directories such as pg_xlog?  If
so, there's no need.  Just make it a symlink to a directory on other
disks and you're done.  If that's not what you mean, can you explain
further?

Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Cluster using tablespaces?

[Erik Jones]

On Nov 28, 2007, at 4:47 PM, Rainer Bauer wrote:


Hello,

is there a way to instruct cluster to store the temporary created  
data on a

different tablespace (i.e. drive)? If not, wouldn't that have a decent
performance impact or is most of the time spend retrieving the data  
in index

order?

Rainer


What temporary created data are you referring to?  Do you mean the  
contents of the the other cluster directories such as pg_xlog?  If  
so, there's no need.  Just make it a symlink to a directory on other  
disks and you're done.  If that's not what you mean, can you explain  
further?


Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] Cluster using tablespaces?

[Rainer Bauer]

Hello,

is there a way to instruct cluster to store the temporary created data on a
different tablespace (i.e. drive)? If not, wouldn't that have a decent
performance impact or is most of the time spend retrieving the data in index
order?

Rainer

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] CLUSTER = slower vacuum?

[Alvaro Herrera]

Phoenix Kiula escribió:
> After I clustered the primary key index of a table with about 300,000
> rows, my vacuum/analyze on that table is taking too long ... over 15
> mins when originally it was 15 seconds! Nothing else has been changed
> with this table. Is clustering not good for vacuums?

No.  Something else must be happening.  Maybe examine the output of
vacuum verbose to see where the time is going?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] CLUSTER = slower vacuum?

[Phoenix Kiula]

After I clustered the primary key index of a table with about 300,000
rows, my vacuum/analyze on that table is taking too long ... over 15
mins when originally it was 15 seconds! Nothing else has been changed
with this table. Is clustering not good for vacuums?

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Cluster and MVCC

[Decibel!]

On Fri, Aug 10, 2007 at 06:34:03PM +0100, Simon Riggs wrote:
> On Fri, 2007-08-10 at 10:02 -0400, Brad Nicholson wrote:
> > I just want to confirm that the cluster/MVCC issues are due to
> > transaction visibility.  Assuming that no concurrent access is happening
> > to a given table when the cluster command is issued (when takes it
> > visibility snapshot), it is safe to cluster that table.  Correct?
> 
> Yes, as long as pre-existing transactions do not then access the
> clustered table. If they do, rows they should have seen will now not be
> visible, yet you won't get an error message to say so.

Don't you also need to be in a serialized transaction?
-- 
Decibel!, aka Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)


pgpM50NoxGTMF.pgp
Description: PGP signature

Re: [GENERAL] Cluster and MVCC

[Simon Riggs]

On Fri, 2007-08-10 at 10:02 -0400, Brad Nicholson wrote:
> I just want to confirm that the cluster/MVCC issues are due to
> transaction visibility.  Assuming that no concurrent access is happening
> to a given table when the cluster command is issued (when takes it
> visibility snapshot), it is safe to cluster that table.  Correct?

Yes, as long as pre-existing transactions do not then access the
clustered table. If they do, rows they should have seen will now not be
visible, yet you won't get an error message to say so.

You can check this by doing something similar to...


create temporary table xids as 
select transactionid from pg_stat_activity a, pg_locks l where a.procpid
= l.pid and l.transactionid is not null;

cluster 

select 'Possible MVCC violation if ' || transactionid || ' touches
clustered table' from pg_locks where transactionid in (select
transactionid from xids);
drop table xids;


-- 
  Simon Riggs
  EnterpriseDB  http://www.enterprisedb.com


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] Cluster and MVCC

[Brad Nicholson]

I just want to confirm that the cluster/MVCC issues are due to
transaction visibility.  Assuming that no concurrent access is happening
to a given table when the cluster command is issued (when takes it
visibility snapshot), it is safe to cluster that table.  Correct?
-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Cluster all tables in database to PK index

[Scott Ribe]

> Besides writing a script that looks through the DDL of all tables, and
> CLUSTERs all tables with PK constraints, is there a quicker way?

Is this really a sensible thing to do? As often as not, you want to cluster
on foreign keys...

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Cluster all tables in database to PK index

[Hannes Dorbath]

Besides writing a script that looks through the DDL of all tables, and 
CLUSTERs all tables with PK constraints, is there a quicker way?


Thanks.


--
Regards,
Hannes Dorbath

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Cluster/redundancy question

[Andrew Sullivan]

On Thu, Oct 13, 2005 at 10:06:51AM -0600, Aly S.P Dharshi wrote:
> Andrew,
> 
>   I disagree, I wouldn't want to contend with all the complexities
> and kludge of Oracle thank you very much. If there was a way to get
> PostgreSQL to do better than the current clustering methods, then why not, it 
> would be a
> big win for us.

I'm not saying we _shouldn't_ go after such functionality (I have
someone reporting to me at work who is in fact doing so).  I'm saying
that if you want that functionality today, you can buy it from one
place, and that's Oracle.  Answers that rely on pretty-good, mostly
works, most of the time, if you use the right table handlers always
and make sure that nobody inserts dates like '2005-02-30', do not
qualify as "a place to buy it from", for the record.  And if "pretty
close" is a good enough answer for you, you don't need this complex
technology at all.  You can use async systems in most cases.

A

PS --  I think MySQL has plenty of good features.  It's a fine
product, loads better than it was in the old days. But the misfeature
of different storage engines, none of which actually achieves all the
other features, is an administration mistake waiting to happen.  It's
what really bothers me about their clustered offering.  Others might
make a different trade-off.  Me, I don't like to be in water over my
head when I'm awakened in the middle of the night.

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
--Dennis Ritchie

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Cluster/redundancy question

[Aly S.P Dharshi]

Andrew,

I disagree, I wouldn't want to contend with all the complexities
and kludge of Oracle thank you very much. If there was a way to get
PostgreSQL to do better than the current clustering methods, then why not, it 
would be a
big win for us.

PostgreSQL *is* an enterprise class DB after all, and we should be
improving upon it to do so and remain so.

Cheers,

Aly.

On Thu, 13 Oct 2005, Andrew Sullivan wrote:

>On Tue, Oct 11, 2005 at 11:38:22AM -0500, Scott Marlowe wrote:
>>
>> Don't get me wrong, if replication is one of the things you need, then
>> consider it, but if you're putting bad data into your database, what
>> good is replicating it gonna do ya?
>
>But if real, ORAC-style clustering is what you need, buy Oracle.  The
>limitations on MySQL's implementation give me the willies.  As Tom
>Waits said, "The large print giveth, and the small print taketh
>away."
>
>A
>
>

-- 
Aly S.P Dharshi
[EMAIL PROTECTED]

 "A good speech is like a good dress
  that's short enough to be interesting
  and long enough to cover the subject"

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Cluster/redundancy question

[Andrew Sullivan]

On Tue, Oct 11, 2005 at 11:38:22AM -0500, Scott Marlowe wrote:
> 
> Don't get me wrong, if replication is one of the things you need, then
> consider it, but if you're putting bad data into your database, what
> good is replicating it gonna do ya?

But if real, ORAC-style clustering is what you need, buy Oracle.  The
limitations on MySQL's implementation give me the willies.  As Tom
Waits said, "The large print giveth, and the small print taketh
away."

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Cluster/redundancy question

[Jim C. Nasby]

Note that pgcluster is statement-based, which has some drawbacks. AFAIK
MySQL's 'clustering' is as well.

Many people use Slony to replicate to many slaves and use pgpool to hit
them. But remember if you do that you need to make sure any statement
that changes data hits your master and not the slaves. Command Prompt's
replication solution might also be useful.

On Tue, Oct 11, 2005 at 10:23:32AM -0600, Aly S.P Dharshi wrote:
> Hello Travis,
> 
>   I don't know if there are a Oracle RAC style cluster system for 
> PGSQL but this software that can do something similar, 
> http://pgcluster.projects.postgresql.org/feature.html may help.
> 
>   You can always use Slony for replication services.
> 
>   Cheers,
> 
>   Aly.
> 
> On Mon, 10 Oct 2005, Travis Brady wrote:
> 
> >All,
> >
> >Forgive me if this has been answered before, but I've searched the archives
> >and the net extensively and have come up mostly empty so far.
> >
> >I'm working at convincing my firm to implement a postgresql database
> >cluster.
> >Specifically, we'd like to get a few machines running to be more available
> >and to protect against any kind of failure.
> >
> >Right now people are leaning toward MySQL and Emic, but I've been
> >evangelizing for postgres for the standard reasons.
> >How does one go about setting this kind of thing up?
> >I've looked at Bizgres and Pervasive but I'm not sure if that's necessarily
> >what we want.
> >
> >thank you,
> >
> >Travis
> >
> 
> -- 
> Aly S.P Dharshi
> [EMAIL PROTECTED]
> 
>"A good speech is like a good dress
> that's short enough to be interesting
> and long enough to cover the subject"
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>   http://www.postgresql.org/docs/faq
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Cluster/redundancy question

[Alex Stapleton]

Don't forget that MySQL replication also has a habit of silently  
failing on you and in my experience needs continuous monitoring to  
make sure it actually keeps reasonably up to date (e.g. not days of  
data behind on the slaves.)


That was a while ago though, maybe they fixed it?
British Telecom use Slony as well, so I guess it can't be *that* bad?

On 11 Oct 2005, at 17:38, Scott Marlowe wrote:


On Mon, 2005-10-10 at 15:16, Travis Brady wrote:


All,

Forgive me if this has been answered before, but I've searched the
archives and the net extensively and have come up mostly empty so  
far.


I'm working at convincing my firm to implement a postgresql database
cluster.
Specifically, we'd like to get a few machines running to be more
available and to protect against any kind of failure.

Right now people are leaning toward MySQL and Emic, but I've been
evangelizing for postgres for the standard reasons.
How does one go about setting this kind of thing up?
I've looked at Bizgres and Pervasive but I'm not sure if that's
necessarily what we want.



If you're looking at the replication systems FIRST, then you've  
missed a

few steps.  Look at what the databases, sans replication, can bring to
the table, and what they lack.

If you MUST have a database that checks input against check  
constraints,

then MySQL is out.  It simply doesn't support them.

If you need a database that converts the number 234987234987234987234
into 2147483647 when you insert it into an int field and doesn't throw
an error or warning, then MySQL should be your choice.

How important is good transactional performance?  MySQL has serious
limitations for certain storage engines and you need to compare those
things before looking at replication.

Don't get me wrong, if replication is one of the things you need, then
consider it, but if you're putting bad data into your database, what
good is replicating it gonna do ya?

---(end of  
broadcast)---

TIP 2: Don't 'kill -9' the postmaster




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Cluster/redundancy question

[Scott Marlowe]

On Mon, 2005-10-10 at 15:16, Travis Brady wrote:
> All,
> 
> Forgive me if this has been answered before, but I've searched the
> archives and the net extensively and have come up mostly empty so far.
> 
> I'm working at convincing my firm to implement a postgresql database
> cluster. 
> Specifically, we'd like to get a few machines running to be more
> available and to protect against any kind of failure.
> 
> Right now people are leaning toward MySQL and Emic, but I've been
> evangelizing for postgres for the standard reasons. 
> How does one go about setting this kind of thing up?
> I've looked at Bizgres and Pervasive but I'm not sure if that's
> necessarily what we want.

If you're looking at the replication systems FIRST, then you've missed a
few steps.  Look at what the databases, sans replication, can bring to
the table, and what they lack.  

If you MUST have a database that checks input against check constraints,
then MySQL is out.  It simply doesn't support them.  

If you need a database that converts the number 234987234987234987234
into 2147483647 when you insert it into an int field and doesn't throw
an error or warning, then MySQL should be your choice.

How important is good transactional performance?  MySQL has serious
limitations for certain storage engines and you need to compare those
things before looking at replication.

Don't get me wrong, if replication is one of the things you need, then
consider it, but if you're putting bad data into your database, what
good is replicating it gonna do ya?

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Cluster/redundancy question

[Aly S.P Dharshi]

Hello Travis,

	I don't know if there are a Oracle RAC style cluster system for 
PGSQL but this software that can do something similar, 
http://pgcluster.projects.postgresql.org/feature.html may help.


You can always use Slony for replication services.

Cheers,

Aly.

On Mon, 10 Oct 2005, Travis Brady wrote:


All,

Forgive me if this has been answered before, but I've searched the archives
and the net extensively and have come up mostly empty so far.

I'm working at convincing my firm to implement a postgresql database
cluster.
Specifically, we'd like to get a few machines running to be more available
and to protect against any kind of failure.

Right now people are leaning toward MySQL and Emic, but I've been
evangelizing for postgres for the standard reasons.
How does one go about setting this kind of thing up?
I've looked at Bizgres and Pervasive but I'm not sure if that's necessarily
what we want.

thank you,

Travis



--
Aly S.P Dharshi
[EMAIL PROTECTED]

 "A good speech is like a good dress
  that's short enough to be interesting
  and long enough to cover the subject"

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] Cluster/redundancy question

[Travis Brady]

All,Forgive me if this has been answered before, but I've searched the archives and the net extensively and have come up mostly empty so far.I'm working at convincing my firm to implement a postgresql database cluster.
Specifically, we'd like to get a few machines running to be more available and to protect against any kind of failure.Right now people are leaning toward MySQL and Emic, but I've been evangelizing for postgres for the standard reasons.
How does one go about setting this kind of thing up?I've looked at Bizgres and Pervasive but I'm not sure if that's necessarily what we want.thank you,Travis

Re: [GENERAL] CLUSTER equivalent

[Alvaro Herrera]

On Thu, Sep 15, 2005 at 05:09:51PM -0400, Kevin Murphy wrote:
> Neil Conway wrote:
> 
> >Kevin Murphy wrote:
> >
> >>I just wanted to confirm that the COPY command always stores data in 
> >>the table in the order in which it appears in the import file.
> >
> >This is not the case -- depending on the content of the FSM, the newly 
> >added rows might be distributed throughout the table.
> 
> How about for a freshly created, empty table -- I should have qualified 
> my original statement.

In that particular case, the answer is yes.  As is if you TRUNCATE the
table just prior to the COPY.

-- 
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"El destino baraja y nosotros jugamos" (A. Schopenhauer)

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] CLUSTER equivalent

[Kevin Murphy]

Neil Conway wrote:


Kevin Murphy wrote:

I just wanted to confirm that the COPY command always stores data in 
the table in the order in which it appears in the import file.



This is not the case -- depending on the content of the FSM, the newly 
added rows might be distributed throughout the table.


How about for a freshly created, empty table -- I should have qualified 
my original statement.


Thanks,
Kevin


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] CLUSTER equivalent

[Neil Conway]

Kevin Murphy wrote:
I just wanted to confirm that the COPY command always stores data in the 
table in the order in which it appears in the import file.


This is not the case -- depending on the content of the FSM, the newly 
added rows might be distributed throughout the table.


-Neil


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] CLUSTER equivalent

[Kevin Murphy]

I just wanted to confirm that the COPY command always stores data in the 
table in the order in which it appears in the import file.


I.e., if the import file is sorted ahead of time, am I correct in 
assuming that the COPY command can have the same effect as CLUSTER'ing 
(or as creating a new table as an ordered select on the loaded table)?  
(Indexes would of course be applied after the data load.)


Thanks,
Kevin Murphy


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] CLUSTER equivalent

[Tom Lane]

Kevin Murphy <[EMAIL PROTECTED]> writes:
> Are the two following options equivalent?
> OPTION A (ordered insert):

> CREATE TABLE table1 (cluster_col TEXT, col2 INTEGER);
> CREATE INDEX idx1 ON table1(cluster_col);
> INSERT INTO table1 (cluster_col, col2) SELECT cluster_col, col2 FROM 
> table1 ORDER BY cluster_col;

> OPTION B (unordered insert followed by CLUSTER):

> CREATE TABLE table1 (cluster_col TEXT, col2 INTEGER);
> CREATE INDEX idx1 ON table1(cluster_col);
> INSERT INTO table1 (cluster_col, col2) SELECT cluster_col, col2 FROM table1;
> CLUSTER idx1 ON table1;

Pretty much, but the first is probably faster.  CLUSTER is not the
speediest possible way of sorting data :-(

> P.S.  On another topic, did I gather correctly from a recent thread that 
> it would be more efficient to define the above table (if it were really 
> only two columns) as:

> create table clustered_tagged_genes (integer pmid, text mention);

> i.e., with the integer field before the text field?

Yeah, putting fixed-width fields first is usually a (marginal) win.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] CLUSTER equivalent

[Kevin Murphy]

Are the two following options equivalent?

OPTION A (ordered insert):

CREATE TABLE table1 (cluster_col TEXT, col2 INTEGER);
CREATE INDEX idx1 ON table1(cluster_col);
INSERT INTO table1 (cluster_col, col2) SELECT cluster_col, col2 FROM 
table1 ORDER BY cluster_col;


OPTION B (unordered insert followed by CLUSTER):

CREATE TABLE table1 (cluster_col TEXT, col2 INTEGER);
CREATE INDEX idx1 ON table1(cluster_col);
INSERT INTO table1 (cluster_col, col2) SELECT cluster_col, col2 FROM table1;
CLUSTER idx1 ON table1;

Thanks,
Kevin Murphy

P.S.  On another topic, did I gather correctly from a recent thread that 
it would be more efficient to define the above table (if it were really 
only two columns) as:


create table clustered_tagged_genes (integer pmid, text mention);

i.e., with the integer field before the text field?


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] CLUSTER

[Adam Kavan]

I have a table that II am constantly inserting into (around 10 times a 
second right now but hope to increase latter).  I hold these rows for a 
week then summarize and delete them.  During that week I need to access 
ranges of these rows based on a timestamp in each row set to now() when I 
insert them.  I have this column indexed but if I let the system run after 
a few weeks it can take a fairly long time to get information from this 
table (for instance the avg() of inserted values in the last 5 minuets 
could take over a minuet to calculate).  If I run CLUSTER on the index of 
the time stamps my time to do this drops down to under 5 seconds again.  I 
checked the plan being used by explain analyze and orginally it was always 
doing seqscans so I set enable_seqscan = FALSE just before I run this 
query, this forces it to use the index and speeds it up quite a bit.

Does anyone know a way I can reorder the database without doing a 
CLUSTER?  It stops all insertions into the table and takes several minuets 
during which time a large backlog builds up.  Or should I be using some 
other method of speeding up the table?

--- Adam Kavan
--- [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] "Cluster" means "tangle" for me

[Tom Lane]

Jeff Eckermann <[EMAIL PROTECTED]> writes:
> I would appreciate any advice on getting out of this strange situation.  My
> table now doesn't exist, but I can't recreate it either (at least under that
> name).

Hmm, was "dedcolo" a temp table?  It looks like clustering a temp table
gets confused.  (In current sources, it's still pretty broken: the
cluster succeeds, but the table is no longer temp afterwards ...
will try to fix this for 7.1.)

I'd suggest that you restart your session, then repeat the vacuum,
and for each table that you get "mdopen" notices about, create an
empty file by that name in the database directory.  Then you'll be
able to drop that table.

regards, tom lane

[GENERAL] "Cluster" means "tangle" for me

[Jeff Eckermann]

I would appreciate any advice on getting out of this strange situation.  My
table now doesn't exist, but I can't recreate it either (at least under that
name).

jeffe@kiyoko=> psql -V
psql (PostgreSQL) 7.0.0

jeffe@kiyoko=> uname -a
FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu Apr 27
10:44:07 CDT 2000 

extracts=# create index dc_et_i on dedcolo (equip_type);
CREATE
extracts=# cluster dc_et_i on dedcolo;
ERROR:  temp_286bbc3 is an index relation
extracts=# drop index dc_et_i;
ERROR:  index "dc_et_i" nonexistent
extracts=# \d dedcolo
Did not find any relation named "dedcolo".
extracts=# vacuum verbose analyze;
(snip)
NOTICE:  mdopen: couldn't open temp_28421e0: No such file or directory
NOTICE:  RelationIdBuildRelation: smgropen(temp_28421e0): No such file or
directory
NOTICE:  --Relation temp_28421e0--
NOTICE:  mdopen: couldn't open temp_28421e0: No such file or directory
ERROR:  cannot open relation temp_28421e0
extracts=# drop table temp_28421e0;
NOTICE:  mdopen: couldn't open temp_28421e0: No such file or directory
NOTICE:  mdopen: couldn't open temp_28421e0: No such file or directory
NOTICE:  mdopen: couldn't open temp_28421e0: No such file or directory