Обсуждение: broke postgres, how to fix??
I tried copying postgres over to a new directory. it was working until I deleted a bunch of databases from the "old" postgres. Lo and behold this somehow broke the "new" copy too. Now when I start with pg_ctl 2013-02-22 18:36:13 EST DETAIL: The database subdirectory "base/1066060" is missing. 2013-02-22 18:36:25 EST FATAL: database "wormmine" does not exist 2013-02-22 18:36:25 EST DETAIL: The database subdirectory "base/1027296" is missing. 2013-02-22 18:37:13 EST FATAL: database "wormmine-dev" does not exist and it won't start... How can I fix this? re-creating these databases with the old postgres didn't work... Also, why did this happen? I created a new data directory for the new postgres, this should be impossible.... I have no ideas left, can anyone help? Thanks in advance, -JD
Did you shut down the 'old' postgres before copying these files? Did you (re)configure the 'new' postgres to set its $PGDATA directory to the location of the 'new' files? On Fri, Feb 22, 2013 at 3:46 PM, JD Wong <jdmswong@gmail.com> wrote: > I tried copying postgres over to a new directory. it was working until I > deleted a bunch of databases from the "old" postgres. Lo and behold this > somehow broke the "new" copy too. > > Now when I start with pg_ctl > 2013-02-22 18:36:13 EST DETAIL: The database subdirectory "base/1066060" is > missing. > 2013-02-22 18:36:25 EST FATAL: database "wormmine" does not exist > 2013-02-22 18:36:25 EST DETAIL: The database subdirectory "base/1027296" is > missing. > 2013-02-22 18:37:13 EST FATAL: database "wormmine-dev" does not exist > > and it won't start... > > How can I fix this? re-creating these databases with the old postgres > didn't work... > > Also, why did this happen? I created a new data directory for the new > postgres, this should be impossible.... > > I have no ideas left, can anyone help? > > Thanks in advance,
On 02/22/2013 03:46 PM, JD Wong wrote: > I tried copying postgres over to a new directory. it was working until > I deleted a bunch of databases from the "old" postgres. Lo and behold > this somehow broke the "new" copy too. Did you actually copy or symlink? > > Now when I start with pg_ctl > 2013-02-22 18:36:13 EST DETAIL: The database subdirectory > "base/1066060" is missing. > 2013-02-22 18:36:25 EST FATAL: database "wormmine" does not exist > 2013-02-22 18:36:25 EST DETAIL: The database subdirectory > "base/1027296" is missing. > 2013-02-22 18:37:13 EST FATAL: database "wormmine-dev" does not exist > > and it won't start... > > How can I fix this? re-creating these databases with the old postgres > didn't work... > > Also, why did this happen? I created a new data directory for the new > postgres, this should be impossible.... It happened so it was not impossible. You sure you deleted from the old directory? > > I have no ideas left, can anyone help? > > Thanks in advance, > -JD -- Adrian Klaver adrian.klaver@gmail.com
On Tue, Feb 26, 2013 at 4:02 PM, JD Wong <jdmswong@gmail.com> wrote: > Hi Adrian, yes I completely copied the config-file and data directories > over. > > Lonnie, I don't remember. I might not have shut down the "old" postgres, > yes I set PGDATA accordingly. That's guaranteed to break everything badly.
On Tue, Feb 26, 2013 at 4:10 PM, JD Wong <jdmswong@gmail.com> wrote: > Hi Adrian, > >> That's guaranteed to break everything badly. > > > Even if I "read only style" copied the files? Do you mind elaborating on why > this happens? ( or point me to relevant documentation ) What is "read only style", and how does postgres know about this? http://www.postgresql.org/docs/9.2/static/backup-file.html > > Thanks, > -JD > > On Tue, Feb 26, 2013 at 7:04 PM, Lonni J Friedman <netllama@gmail.com> > wrote: >> >> On Tue, Feb 26, 2013 at 4:02 PM, JD Wong <jdmswong@gmail.com> wrote: >> > Hi Adrian, yes I completely copied the config-file and data directories >> > over. >> > >> > Lonnie, I don't remember. I might not have shut down the "old" >> > postgres, >> > yes I set PGDATA accordingly. > > >> >> >> >> That's guaranteed to break everything badly. > > -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand https://netllama.linux-sxs.org
On 02/26/2013 04:12 PM, Lonni J Friedman wrote: > On Tue, Feb 26, 2013 at 4:10 PM, JD Wong <jdmswong@gmail.com> wrote: >> Hi Adrian, >> >>> That's guaranteed to break everything badly. >> >> >> Even if I "read only style" copied the files? Do you mind elaborating on why >> this happens? ( or point me to relevant documentation ) > > What is "read only style", and how does postgres know about this? > > http://www.postgresql.org/docs/9.2/static/backup-file.html I think we are going to need a more complete description of what happened. 1) The OS involved. 2) Postgres version of old and new directories 3) Location of old and new directories. 4) What exactly was done to get from old to new. In the event things are real and truly broken, do you have backups? > > -- Adrian Klaver adrian.klaver@gmail.com
Hi Adrian, That's guaranteed to break everything badly. > Even if I "read only style" copied the files? Do you mind elaborating on why this happens? ( or point me to relevant documentation ) Thanks, -JD On Tue, Feb 26, 2013 at 7:04 PM, Lonni J Friedman <netllama@gmail.com>wrote: > On Tue, Feb 26, 2013 at 4:02 PM, JD Wong <jdmswong@gmail.com> wrote: > > Hi Adrian, yes I completely copied the config-file and data directories > > over. > > > > Lonnie, I don't remember. I might not have shut down the "old" postgres, > > yes I set PGDATA accordingly. > > > That's guaranteed to break everything badly. >
Hi Adrian, yes I completely copied the config-file and data directories over. Lonnie, I don't remember. I might not have shut down the "old" postgres, yes I set PGDATA accordingly. To sum things up, I created a situation where I would use the service to start "old" postgres and pg_ctl to start the "new" one. Thanks! -JD On Tue, Feb 26, 2013 at 6:32 PM, Lonni J Friedman <netllama@gmail.com>wrote: > Did you shut down the 'old' postgres before copying these files? > Did you (re)configure the 'new' postgres to set its $PGDATA directory > to the location of the 'new' files? > > On Fri, Feb 22, 2013 at 3:46 PM, JD Wong <jdmswong@gmail.com> wrote: > > I tried copying postgres over to a new directory. it was working until I > > deleted a bunch of databases from the "old" postgres. Lo and behold this > > somehow broke the "new" copy too. > > > > Now when I start with pg_ctl > > 2013-02-22 18:36:13 EST DETAIL: The database subdirectory > "base/1066060" is > > missing. > > 2013-02-22 18:36:25 EST FATAL: database "wormmine" does not exist > > 2013-02-22 18:36:25 EST DETAIL: The database subdirectory > "base/1027296" is > > missing. > > 2013-02-22 18:37:13 EST FATAL: database "wormmine-dev" does not exist > > > > and it won't start... > > > > How can I fix this? re-creating these databases with the old postgres > > didn't work... > > > > Also, why did this happen? I created a new data directory for the new > > postgres, this should be impossible.... > > > > I have no ideas left, can anyone help? > > > > Thanks in advance, >
JD Wong wrote: >>> Hi Adrian, yes I completely copied the config-file and data directories >>> over. >> That's guaranteed to break everything badly. > Even if I "read only style" copied the files? Do you mind elaborating on why this happens? ( or point > me to relevant documentation ) The problem is that if you copy the files of a running database, these files will change while they are being copied. This can result in unusable contents. Also, to function properly, the different files in a PostgreSQL data directory must be consistent with each other. If you copy one file after the other, files that are copied later might belong to a different state of the database than earlier files. You either have to shutdown the database before copying (a filesystem level offline backup) or use some instantaneous snapshotting technique that your file system might offer. In the latter case PostgreSQL should perform crash recovery and eventually reach a consistent state. Yours, Laurenz Albe
On 03/01/2013 02:31 AM, Albe Laurenz wrote:
> JD Wong wrote:
>>>> Hi Adrian, yes I completely copied the config-file and data directories
>>>> over.
>
>>> That's guaranteed to break everything badly.
>
>> Even if I "read only style" copied the files? Do you mind elaborating on why this happens? ( or point
>> me to relevant documentation )
>
> You either have to shutdown the database before copying
> (a filesystem level offline backup) or use some instantaneous
> snapshotting technique that your file system might offer.
> In the latter case PostgreSQL should perform crash recovery
> and eventually reach a consistent state.
>
> Yours,
> Laurenz Albe
Of course, Postgres has some built in capabilities in this regard
I have a script to start our replication server that does something like:
su - postgres -c "psql -c \"checkpoint; select pg_switch_xlog();\";"
su - postgres -c "psql -c \"SELECT pg_start_backup('backup', true)\";"
rsync -av /var/lib/pgsql/ remote-system.example.com:/var/lib/pgsql/
su - postgres -c "psql -c \"SELECT pg_stop_backup()\"
Thank you everybody for your help,
This problem has been resolved, in part to your insights.
All the best,
-JD
On Fri, Mar 1, 2013 at 4:31 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
JD Wong wrote:
>>> Hi Adrian, yes I completely copied the config-file and data directories
>>> over.>> That's guaranteed to break everything badly.The problem is that if you copy the files of a running database,
> Even if I "read only style" copied the files? Do you mind elaborating on why this happens? ( or point
> me to relevant documentation )
these files will change while they are being copied.
This can result in unusable contents.
Also, to function properly, the different files
in a PostgreSQL data directory must be consistent with each other.
If you copy one file after the other, files that are copied later
might belong to a different state of the database than earlier files.
You either have to shutdown the database before copying
(a filesystem level offline backup) or use some instantaneous
snapshotting technique that your file system might offer.
In the latter case PostgreSQL should perform crash recovery
and eventually reach a consistent state.
Yours,
Laurenz Albe