Thread: file system level backup
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).
however, postgresql b won't start ("not listening")?
so i restored postgresql b's old /data/, but it still won't start?!
help!
(longer answer - what's the best way to copy data from a to b?)
however, postgresql b won't start ("not listening")?
so i restored postgresql b's old /data/, but it still won't start?!
help!
(longer answer - what's the best way to copy data from a to b?)
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
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> > > 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
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?
> 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.
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?
> 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
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?
> no
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?
> 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
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
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.
- 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