broke postgres, how to fix??

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

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

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,

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

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:

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.

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

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.

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

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

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

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

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

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

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:

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()\"

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