file system level backup

On 01/04/2014 12:54 PM, zach cruise wrote:

so i installed 9.3 x64 on two windows 2008 servers from one set of
installation files. then i created databases on postgresql a, shutdown
all the servers, copied /data/ (from "show data_directory") from
postgresql a to postgresql b. in short, i did a full file system level
backup (http://www.postgresql.org/docs/9.3/static/backup-file.html).

Questions:

1) Both servers are x64?

2) What installation files(Graphical installer, source,etc)?

3) Does the data directory have the config files?

however, postgresql b won't start ("not listening")?

4) How are you starting?

5) What is the exact error message and where are you seeing it?

so i restored postgresql b's old /data/, but it still won't start?!

6) So there was an existing Postgres instance at b?

7) What version was it?

help!

(longer answer - what's the best way to copy data from a to b?)

--
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

Questions:

1) Both servers are x64?

yes

2) What installation files(Graphical installer, source,etc)?

postgresql-9.3.0-1-windows-x64 from enterprisedb.com

3) Does the data directory have the config files?

yes

however, postgresql b won't start ("not listening")?

4) How are you starting?

pgadmin -> start server

5) What is the exact error message and where are you seeing it?

"server not listening on port 5432" in cmd ('netstat -ab | find "5432"'

also returns empty)

so i restored postgresql b's old /data/, but it still won't start?!

6) So there was an existing Postgres instance at b?

yes. two installations - a and b.

7) What version was it?

both are 9.3.0-1 from the same installation file (above).

fyi, pg_bouncer process was running even after the server shutdown when i
did the copy (since size is not an issue, i could copy the entire
/postgresql/?).

On 01/04/2014 01:20 PM, zach cruise wrote:

Questions:

1) Both servers are x64?

yes

2) What installation files(Graphical installer, source,etc)?

postgresql-9.3.0-1-windows-x64 from enterprisedb.com

<http://enterprisedb.com&gt;

3) Does the data directory have the config files?

yes

however, postgresql b won't start ("not listening")?

4) How are you starting?

pgadmin -> start server

5) What is the exact error message and where are you seeing it?

"server not listening on port 5432" in cmd ('netstat -ab | find

"5432"' also returns empty)

Have you checked what port is set up in postgresql.conf?

I am not that familiar with Windows and pgAdmin so I will resort to the
docs:

Does any of the below apply?

http://www.pgadmin.org/docs/1.8/connect.html

"If pgAdmin is running on a Windows machine, it can control the
postmaster service if you have enough access rights. Enter the name of
the service. In case of a remote server, it must be prepended by the
machine name (e.g. PSE1\pgsql-8.0). pgAdmin will automatically discover
services running on your local machine. "

so i restored postgresql b's old /data/, but it still won't start?!

6) So there was an existing Postgres instance at b?

yes. two installations - a and b.

I am still not understanding. You have a 2008 server at a and one at b.
You copied the Postgres data files from 2008 server a to 2008 server b.
This did not work, so you say you restored the data directory on server
b from an old copy of the data directory at b.

So where did that old copy come from?

7) What version was it?

both are 9.3.0-1 from the same installation file (above).

fyi, pg_bouncer process was running even after the server shutdown when
i did the copy (since size is not an issue, i could copy the entire
/postgresql/?).

--
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

Questions:

1) Both servers are x64?

yes

2) What installation files(Graphical installer, source,etc)?

postgresql-9.3.0-1-windows-x64 from enterprisedb.com

<http://enterprisedb.com&gt;

3) Does the data directory have the config files?

yes

however, postgresql b won't start ("not listening")?

4) How are you starting?

pgadmin -> start server

5) What is the exact error message and where are you seeing it?

"server not listening on port 5432" in cmd ('netstat -ab | find

"5432"' also returns empty)

Have you checked what port is set up in postgresql.conf?

yes (port = 5432)

I am not that familiar with Windows and pgAdmin so I will resort to the
docs:

Does any of the below apply?

http://www.pgadmin.org/docs/1.8/connect.html

"If pgAdmin is running on a Windows machine, it can control the postmaster
service if you have enough access rights. Enter the name of the service. In
case of a remote server, it must be prepended by the machine name (e.g.
PSE1\pgsql-8.0). pgAdmin will automatically discover services running on
your local machine. "

thanks. it doesn't help. i also tried pg_ctl without luck (

http://www.postgresql.org/docs/9.3/static/app-pg-ctl.html).

so i restored postgresql b's old /data/, but it still won't start?!

6) So there was an existing Postgres instance at b?

yes. two installations - a and b.

I am still not understanding. You have a 2008 server at a and one at b. You
copied the Postgres data files from 2008 server a to 2008 server b. This
did not work, so you say you restored the data directory on server b from
an old copy of the data directory at b.

So where did that old copy come from?

short answer - from b itself (whatever was created right after

installation).

long answer - 2008 servers at a and b. postgres 9.3s at a and b. added

databases to postgres-a (postgres-b is empty/new). shutdown postgres-a and
b. copied data-a and b to a network shared between server-a and b. deleted
data-b on server-b. copied data-a from network to server-b.

restarted postgres-a and postgres-b. postgres-a succeeds. postgres-b

fails.

so deleted data on server-b. restored data-b from network ("old copy") to

server-b. restarted postgres-b. postgres-b still fails.

On 01/04/2014 02:13 PM, zach cruise wrote:

I am still not understanding. You have a 2008 server at a and one at b.
You copied the Postgres data files from 2008 server a to 2008 server b.
This did not work, so you say you restored the data directory on server
b from an old copy of the data directory at b.

So where did that old copy come from?

short answer - from b itself (whatever was created right after

installation).

long answer - 2008 servers at a and b. postgres 9.3s at a and b.

added databases to postgres-a (postgres-b is empty/new). shutdown
postgres-a and b. copied data-a and b to a network shared between
server-a and b. deleted data-b on server-b. copied data-a from network
to server-b.

restarted postgres-a and postgres-b. postgres-a succeeds. postgres-b

fails.

so deleted data on server-b. restored data-b from network ("old

copy") to server-b. restarted postgres-b. postgres-b still fails.

So is there anything in the Windows system logs?

Does the Task Manager show another Postgres running?

--
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

I am still not understanding. You have a 2008 server at a and one at b.
You copied the Postgres data files from 2008 server a to 2008 server b.
This did not work, so you say you restored the data directory on server
b from an old copy of the data directory at b.

So where did that old copy come from?

short answer - from b itself (whatever was created right after

installation).

long answer - 2008 servers at a and b. postgres 9.3s at a and b.

added databases to postgres-a (postgres-b is empty/new). shutdown
postgres-a and b. copied data-a and b to a network shared between
server-a and b. deleted data-b on server-b. copied data-a from network
to server-b.

restarted postgres-a and postgres-b. postgres-a succeeds. postgres-b

fails.

so deleted data on server-b. restored data-b from network ("old

copy") to server-b. restarted postgres-b. postgres-b still fails.

So is there anything in the Windows system logs?

looks like file system level backups don't work well on windows because

they get corrupted during transfer.

Does the Task Manager show another Postgres running?

Show quoted text

no

On 01/04/2014 04:37 PM, zach cruise wrote:

So is there anything in the Windows system logs?

looks like file system level backups don't work well on windows

because they get corrupted during transfer.

Whoa, how did we get to that conclusion?

Does the Task Manager show another Postgres running?

no

--
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

I am not sure if you have already answered it and I have somehow missed it:
- Are these 'a' and 'b' on two different servers? ( I think they are on
different servers)
- Did you stop the server on 'b' before you replaced the files and
attempted a startup?

thanks. it doesn't help. i also tried pg_ctl without luck (

http://www.postgresql.org/docs/9.3/static/app-pg-ctl.html).

What exact pg_ctl command you used?

Regards
Sameer

On 01/04/2014 06:41 PM, zach cruise wrote:

CCing list:

So is there anything in the Windows system logs?

looks like file system level backups don't work well on windows

because they get corrupted during transfer.

Whoa, how did we get to that conclusion?

as a safety, i had also renamed old data-b. when copying old data-b

from the network didn't work, i renamed it back. and that worked.

Well all that proves is that there is some difference between the data
directories. That could be corruption, it could be something else.

Since we are on the subject of corruption and this is Windows, do you
have anti-virus software running on these machines?

If so, is it pointed at the directories in question?

do you (or others) have a fix for how to guarantee file system level

backups on windows?

--
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

I am not sure if you have already answered it and I have somehow missed it:
- Are these 'a' and 'b' on two different servers? ( I think they are on
different servers)

yes

- Did you stop the server on 'b' before you replaced the files and
attempted a startup?

yes

thanks. it doesn't help. i also tried pg_ctl without luck (

http://www.postgresql.org/docs/9.3/static/app-pg-ctl.html).

What exact pg_ctl command you used?

"pg_ctl start" and "pg_ctl stop"

---

So is there anything in the Windows system logs?

looks like file system level backups don't work well on windows

because they get corrupted during transfer.

Whoa, how did we get to that conclusion?

as a safety, i had also renamed old data-b. when copying old data-b

from the network didn't work, i renamed it back. and that worked.

Well all that proves is that there is some difference between the data
directories. That could be corruption, it could be something else.

Since we are on the subject of corruption and this is Windows, do you have
anti-virus software running on these machines?

If so, is it pointed at the directories in question?

yes (symantec) but it is pointed to both directories, old data-b and

renamed old data-b, so i doubt if that is the issue.

On 01/06/2014 07:03 AM, zach cruise wrote:

I am not sure if you have already answered it and I have somehow missed it:
- Are these 'a' and 'b' on two different servers? ( I think they are on
different servers)

yes

- Did you stop the server on 'b' before you replaced the files and
attempted a startup?

yes

thanks. it doesn't help. i also tried pg_ctl without luck

(http://www.postgresql.org/docs/9.3/static/app-pg-ctl.html).

What exact pg_ctl command you used?

"pg_ctl start" and "pg_ctl stop"

---

So is there anything in the Windows system logs?

looks like file system level backups don't work well on windows

because they get corrupted during transfer.

Whoa, how did we get to that conclusion?

as a safety, i had also renamed old data-b. when copying old data-b

from the network didn't work, i renamed it back. and that worked.

Well all that proves is that there is some difference between the data
directories. That could be corruption, it could be something else.

Since we are on the subject of corruption and this is Windows, do you
have anti-virus software running on these machines?

If so, is it pointed at the directories in question?

yes (symantec) but it is pointed to both directories, old data-b and

renamed old data-b, so i doubt if that is the issue.

I should have been clearer, I was thinking of the data directories on
both a and b machines. In any case anti-virus software corruption of
files in data directories is a known issue. Feel free to search the
archives for the reports:) Whether it happens depends on how active and
aggressive the a/v software is.

I am leaning to the idea that when you did the original copy of data
directories from a and b the respective servers where not completely
shut down. You mentioned that pgBouncer was running when you did the
copying. It could be it was holding connections open to the servers that
left them running. At present you say the a server is running. As a test
I would suggest shutting it down(and verifying it is not running) do a
copy to b and try again to start the b server.

--
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