Обсуждение: Relocated Postgres

El mié, 11-03-2015 a las 01:02 +0000, Rossi, Maria escribió:
> HI,
>

Hi,
>
>
> I relocated an postgres instance.  The pg_data, pg_tranlog,
> pg_hba.conf, etc were copied to new directories.

did you stop the instance, before start the copy ?
>   Then, I updated the postgresql.conf accordingly.  Postgres started
> ok, and I can connect.   However when I stopped it,  the following msg
> appeared at the errorlog:
>
>
>
> cp: overwrite
> `/dba/psg/gs07/pg_tranlog/pg_archlog/00000001000000000000004C'? <
> 2015-03-10 20:50:38 EDT >LOG:  database system is shut down
>
Could you share with us archive_command and archive_mode of
postgresql.conf ?
It seems than you are using "cp -i" in your archive_command, doesn't
it ?

>
>
> Postgres proceeded to shutdown.
>
>
>
> ‘ls’ showed that the file still have the old timestamp, which is the
> timestamp when I copied it over:
>
>
>
> ls -ltr /dba/psg/gs07/pg_tranlog/pg_archlog/00000001000000000000004B
>
> -rw------- 1 gs07 dbagroup 16777216 Mar 10
> 12:07 /dba/psg/gs07/pg_tranlog/pg_archlog/0000000100
>
>
>
> I did not have any transctions, though between the start-up and
> shutdown.  I would have expected the timestamp of the logfile to have
> been changed.
>
> Looks like it was waiting for a response to ‘cp’.
>
>
>
> I am concerned that if there were any transactions, I would have lost
> it.
>
> Pls advice.
>
>
>
> Thanks.
>
>
>
> Maria
>
>
>
>

El mié, 11-03-2015 a las 17:33 +0000, Rossi, Maria escribió:
> Jaime,
>
> Yes, I stopped the instance before the copy.  Here's the archive at the postgressql.conf:
>
> archive_mode = on               # allows archiving to be done
> archive_command = 'cp -i "%p" /dba/psg/gs07/pg_tranlog/pg_archlog/"%f"'

I recommend you to avoid using modifier -i of cp command, because it
will ask you if you want to overwrite a file (interactive mode inside a
script ?) and although the file isn't overwriten cp returns with 0 exit
code, and it means than archiver process has properly archived
00000001000000000000004C and that is false.
why are you using double quotes on %p and %f?

>
>
> Also,   Here’s how I relocated.
> 1)     Shutdown postgres
> 2)    Copied pg_data to another directory
> 3)    Copied pg_xlog to yet another directory  (we wanted to put the pg_data and tranlog in separate directories)
> 4)    At the new pg_data  directory, create symbolic link
>     ln -s /dba/pgsql /gs07/pg_tranlog/pg_xlog pg_xlog    (this is where my problem started,  I had it pointing to the
oldlocation) 
I don't understand why are you using this command with threes arguments,
I have never used before. But It seems that symbolic links weren't
created correctly. The wal file: 00000001000000000000004C already exists
in pg_xlog and maybe you're pointing pg_archlog to pg_xlog directory and
this is why archive_command throwed errors.

> 5)    Updated postgressql.conf   for the new location (unaware that my symbolic link was pointing to the  old
location)
> 6)    Start postgres
>
> So any logs went to the old location.
>
> Thanks/

Regards, Jaime
>
> Maria A Rossi
> Database Administration
> PGDS (US)
> email: maria.rossi@us.pgds.com
> cell phone: 517.256.4392
> work phone: 517.367.3099
> work phone extension: 23099
>
> -----Original Message-----
> From: jaime soler [mailto:jaime.soler@gmail.com]
> Sent: Wednesday, March 11, 2015 12:35 PM
> To: Rossi, Maria
> Cc: 'pgsql-admin@postgresql.org'
> Subject: Re: [ADMIN] Relocated Postgres
>
> El mié, 11-03-2015 a las 01:02 +0000, Rossi, Maria escribió:
> > HI,
> >
>
> Hi,
> >
> >
> > I relocated an postgres instance.  The pg_data, pg_tranlog,
> > pg_hba.conf, etc were copied to new directories.
>
> did you stop the instance, before start the copy ?
> >   Then, I updated the postgresql.conf accordingly.  Postgres started
> > ok, and I can connect.   However when I stopped it,  the following msg
> > appeared at the errorlog:
> >
> >
> >
> > cp: overwrite
> > `/dba/psg/gs07/pg_tranlog/pg_archlog/00000001000000000000004C'? <
> > 2015-03-10 20:50:38 EDT >LOG:  database system is shut down
> >
> Could you share with us archive_command and archive_mode of postgresql.conf ?
> It seems than you are using "cp -i" in your archive_command, doesn't it ?
>
> >
> >
> > Postgres proceeded to shutdown.
> >
> >
> >
> > ‘ls’ showed that the file still have the old timestamp, which is the
> > timestamp when I copied it over:
> >
> >
> >
> > ls -ltr /dba/psg/gs07/pg_tranlog/pg_archlog/00000001000000000000004B
> >
> > -rw------- 1 gs07 dbagroup 16777216 Mar 10
> > 12:07 /dba/psg/gs07/pg_tranlog/pg_archlog/0000000100
> >
> >
> >
> > I did not have any transctions, though between the start-up and
> > shutdown.  I would have expected the timestamp of the logfile to have
> > been changed.
> >
> > Looks like it was waiting for a response to ‘cp’.
> >
> >
> >
> > I am concerned that if there were any transactions, I would have lost
> > it.
> >
> > Pls advice.
> >
> >
> >
> > Thanks.
> >
> >
> >
> > Maria
> >
> >
> >
> >
>
>

Jaime,

I removed the '-i' from the cp command, shutdown and the cp overwrite msg did not come up!! Yay!! Thanks for that.

Interestingly though, we have several postgres instances and all of them  has the 'cp -i' and none of that had this
issue.  
 
I actually copied the postgressql.conf from an existing instance,  so the  'cp -i ' was propagated.  

As far as the double quote, I don't know why. I just copied and the example at the postgres doc used it also.
Thanks again.

Maria A Rossi
Database Administration
PGDS (US)
email: maria.rossi@us.pgds.com
cell phone: 517.256.4392
work phone: 517.367.3099
work phone extension: 23099

-----Original Message-----
From: jaime soler [mailto:jaime.soler@gmail.com] 
Sent: Wednesday, March 11, 2015 2:40 PM
To: Rossi, Maria
Cc: 'pgsql-admin@postgresql.org'
Subject: Re: [ADMIN] Relocated Postgres

El mié, 11-03-2015 a las 17:33 +0000, Rossi, Maria escribió:
> Jaime,
> 
> Yes, I stopped the instance before the copy.  Here's the archive at the postgressql.conf:
> 
> archive_mode = on               # allows archiving to be done
> archive_command = 'cp -i "%p" /dba/psg/gs07/pg_tranlog/pg_archlog/"%f"'  

I recommend you to avoid using modifier -i of cp command, because it will ask you if you want to overwrite a file
(interactivemode inside a script ?) and although the file isn't overwriten cp returns with 0 exit code, and it means
thanarchiver process has properly archived 00000001000000000000004C and that is false.
 
why are you using double quotes on %p and %f?

> 
> 
> Also,   Here’s how I relocated.
> 1)     Shutdown postgres
> 2)    Copied pg_data to another directory
> 3)    Copied pg_xlog to yet another directory  (we wanted to put the pg_data and tranlog in separate directories)
> 4)    At the new pg_data  directory, create symbolic link
>     ln -s /dba/pgsql /gs07/pg_tranlog/pg_xlog pg_xlog    (this is where my problem started,  I had it pointing to the
oldlocation)
 
I don't understand why are you using this command with threes arguments, I have never used before. But It seems that
symboliclinks weren't created correctly. The wal file: 00000001000000000000004C already exists in pg_xlog and maybe
you'repointing pg_archlog to pg_xlog directory and this is why archive_command throwed errors.  
 

> 5)    Updated postgressql.conf   for the new location (unaware that my symbolic link was pointing to the  old
location)
> 6)    Start postgres
> 
> So any logs went to the old location.  
> 
> Thanks/

Regards, Jaime
> 
> Maria A Rossi
> Database Administration
> PGDS (US)
> email: maria.rossi@us.pgds.com
> cell phone: 517.256.4392
> work phone: 517.367.3099
> work phone extension: 23099
> 
> -----Original Message-----
> From: jaime soler [mailto:jaime.soler@gmail.com]
> Sent: Wednesday, March 11, 2015 12:35 PM
> To: Rossi, Maria
> Cc: 'pgsql-admin@postgresql.org'
> Subject: Re: [ADMIN] Relocated Postgres
> 
> El mié, 11-03-2015 a las 01:02 +0000, Rossi, Maria escribió:
> > HI,
> > 
> 
> Hi,
> >  
> > 
> > I relocated an postgres instance.  The pg_data, pg_tranlog, 
> > pg_hba.conf, etc were copied to new directories.
> 
> did you stop the instance, before start the copy ?
> >   Then, I updated the postgresql.conf accordingly.  Postgres started
> > ok, and I can connect.   However when I stopped it,  the following msg
> > appeared at the errorlog:
> > 
> >  
> > 
> > cp: overwrite
> > `/dba/psg/gs07/pg_tranlog/pg_archlog/00000001000000000000004C'? <
> > 2015-03-10 20:50:38 EDT >LOG:  database system is shut down
> > 
> Could you share with us archive_command and archive_mode of postgresql.conf ?
> It seems than you are using "cp -i" in your archive_command, doesn't it ?  
> 
> >  
> > 
> > Postgres proceeded to shutdown.   
> > 
> >  
> > 
> > ‘ls’ showed that the file still have the old timestamp, which is the 
> > timestamp when I copied it over:
> > 
> >  
> > 
> > ls -ltr /dba/psg/gs07/pg_tranlog/pg_archlog/00000001000000000000004B
> > 
> > -rw------- 1 gs07 dbagroup 16777216 Mar 10
> > 12:07 /dba/psg/gs07/pg_tranlog/pg_archlog/0000000100
> > 
> >  
> > 
> > I did not have any transctions, though between the start-up and 
> > shutdown.  I would have expected the timestamp of the logfile to 
> > have been changed.
> > 
> > Looks like it was waiting for a response to ‘cp’.
> > 
> >  
> > 
> > I am concerned that if there were any transactions, I would have 
> > lost it.
> > 
> > Pls advice.
> > 
> >  
> > 
> > Thanks.
> > 
> >  
> > 
> > Maria
> > 
> >  
> > 
> > 
> 
>