Обсуждение: 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

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

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

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

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

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