Обсуждение: PostgreSQL 9.0.1 PITR can not copy WAL file
My PITR work well from 01/01/2011 to 06/01/2011. At 06/01/2011 postgresql log have issue
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_19"."cdvt13newtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_49"."tmpct70s" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_34"."kmtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_34"."dmgiabancttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_34"."dmckcttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_58"."cdvt13newtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_53"."dmgiabancttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_120"."cdvt13newtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_119"."dmgiabancttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_119"."dmckcttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_119"."kmtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_22"."dmgiabancttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_18"."cdvt13newtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_15"."cdvt13newtmp" in database "cpnvn_data"
2011-01-06 08:27:54 ICT LOG: archive command failed with exit code 1
2011-01-06 08:27:54 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:27:55 ICT LOG: archive command failed with exit code 1
2011-01-06 08:27:55 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:27:56 ICT LOG: archive command failed with exit code 1
2011-01-06 08:27:56 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:27:56 ICT WARNING: transaction log file "00000001000000040000005E" could not be archived: too many failures
...
2011-01-06 08:28:56 ICT LOG: archive command failed with exit code 1
2011-01-06 08:28:56 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:28:57 ICT LOG: archive command failed with exit code 1
2011-01-06 08:28:57 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:28:58 ICT LOG: archive command failed with exit code 1
2011-01-06 08:28:58 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:28:58 ICT WARNING: transaction log file "00000001000000040000005E" could not be archived: too many failures
And my pg_xlog can not copy to D:/3SDATABACKUP/PITR/WAL from 06/01/2011. How to fix error ? Please help me
Here is my Archive
# - Archiving -
archive_mode = on # allows archiving to be done
# (change requires restart)
archive_command = 'copy %p D:/3SDATABACKUP/PITR/WAL/%f' # command to use to archive a logfile segment
Thanks in advance
Tuan Hoang ANh
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_19"."cdvt13newtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_49"."tmpct70s" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_34"."kmtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_34"."dmgiabancttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_34"."dmckcttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_58"."cdvt13newtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_53"."dmgiabancttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_120"."cdvt13newtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_119"."dmgiabancttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_119"."dmckcttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_119"."kmtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_22"."dmgiabancttmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_18"."cdvt13newtmp" in database "cpnvn_data"
2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table "pg_temp_15"."cdvt13newtmp" in database "cpnvn_data"
2011-01-06 08:27:54 ICT LOG: archive command failed with exit code 1
2011-01-06 08:27:54 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:27:55 ICT LOG: archive command failed with exit code 1
2011-01-06 08:27:55 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:27:56 ICT LOG: archive command failed with exit code 1
2011-01-06 08:27:56 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:27:56 ICT WARNING: transaction log file "00000001000000040000005E" could not be archived: too many failures
...
2011-01-06 08:28:56 ICT LOG: archive command failed with exit code 1
2011-01-06 08:28:56 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:28:57 ICT LOG: archive command failed with exit code 1
2011-01-06 08:28:57 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:28:58 ICT LOG: archive command failed with exit code 1
2011-01-06 08:28:58 ICT DETAIL: The failed archive command was: copy pg_xlog\00000001000000040000005E D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
2011-01-06 08:28:58 ICT WARNING: transaction log file "00000001000000040000005E" could not be archived: too many failures
And my pg_xlog can not copy to D:/3SDATABACKUP/PITR/WAL from 06/01/2011. How to fix error ? Please help me
Here is my Archive
# - Archiving -
archive_mode = on # allows archiving to be done
# (change requires restart)
archive_command = 'copy %p D:/3SDATABACKUP/PITR/WAL/%f' # command to use to archive a logfile segment
Thanks in advance
Tuan Hoang ANh
* tuanhoanganh wrote: > My PITR work well from 01/01/2011 to 06/01/2011. At 06/01/2011 > postgresql log have issue > 2011-01-06 08:27:54 ICT LOG: archive command failed with exit code 1 > 2011-01-06 08:27:54 ICT DETAIL: The failed archive command was: copy > pg_xlog\00000001000000040000005E > D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E [... lots more ...] > 2011-01-06 08:28:58 ICT WARNING: transaction log file > "00000001000000040000005E" could not be archived: too many failures > > And my pg_xlog can not copy to D:/3SDATABACKUP/PITR/WAL from 06/01/2011. > How to fix error ? Please help me Some possible reasons: - Target disk full - PostgreSQL user does not have write privilege for the target directory - Target file exists already (then you have a bigger problem) - PostgreSQL user does not have full control privileges for the source file (the copy command needs them) If you can stop your server, do so, then try to copy the file yourself on the command line. If that fails as well, you will get a better error message. If the server must stay up, copy the file to somwhere else on D: (assuming you don't have a volume mounted somewhere in the path). If you can copy the file yourself, you have a permissions problem. Make sure the PostgreSQL service user has full control on both the source and target directories. -- Christian
I have checked your solution.
- Target disk full : No
- PostgreSQL user does not have write privilege for the target directory : No
- Target file exists already (then you have a bigger problem) : Last file in D:/3SDATABACKUP/PITR/WAL is 00000001000000040000005D
- PostgreSQL user does not have full control privileges for the source
file (the copy command needs them) : i switch to user postgres an copy 00000001000000040000005E from source to d:\temp and create new text file on D:/3SDATABACKUP/PITR/WAL it is ok. No access denied
- Target disk full : No
- PostgreSQL user does not have write privilege for the target directory : No
- Target file exists already (then you have a bigger problem) : Last file in D:/3SDATABACKUP/PITR/WAL is 00000001000000040000005D
- PostgreSQL user does not have full control privileges for the source
file (the copy command needs them) : i switch to user postgres an copy 00000001000000040000005E from source to d:\temp and create new text file on D:/3SDATABACKUP/PITR/WAL it is ok. No access denied
On Wed, Jan 19, 2011 at 6:56 PM, Christian Ullrich <chris@chrullrich.net> wrote:
* tuanhoanganh wrote:My PITR work well from 01/01/2011 to 06/01/2011. At 06/01/2011
postgresql log have issue[... lots more ...]2011-01-06 08:27:54 ICT LOG: archive command failed with exit code 1
2011-01-06 08:27:54 ICT DETAIL: The failed archive command was: copy
pg_xlog\00000001000000040000005E
D:/3SDATABACKUP/PITR/WAL/00000001000000040000005ESome possible reasons:2011-01-06 08:28:58 ICT WARNING: transaction log file
"00000001000000040000005E" could not be archived: too many failures
And my pg_xlog can not copy to D:/3SDATABACKUP/PITR/WAL from 06/01/2011.
How to fix error ? Please help me
- Target disk full
- PostgreSQL user does not have write privilege for the target directory
- Target file exists already (then you have a bigger problem)
- PostgreSQL user does not have full control privileges for the source
file (the copy command needs them)
If you can stop your server, do so, then try to copy the file yourself on the command line. If that fails as well, you will get a better error message. If the server must stay up, copy the file to somwhere else on D: (assuming you don't have a volume mounted somewhere in the path).
If you can copy the file yourself, you have a permissions problem. Make sure the PostgreSQL service user has full control on both the source and target directories.
--
Christian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
* tuanhoanganh wrote: > I have checked your solution. > - Target disk full : No > - PostgreSQL user does not have write privilege for the target directory > : No > - Target file exists already (then you have a bigger problem) : Last > file in D:/3SDATABACKUP/PITR/WAL is 00000001000000040000005D > - PostgreSQL user does not have full control privileges for the source > file (the copy command needs them) : i switch to user postgres an copy > 00000001000000040000005E from source to d:\temp and create new text file > on D:/3SDATABACKUP/PITR/WAL it is ok. No access denied So when PostgreSQL runs "copy 000...5E D:\...", it fails, and when you do the same thing as the PostgreSQL user, it works. Interesting. Try increasing the log level in postgresql.conf to see if it logs the error message from copy, or try xcopy instead of copy. Do you have some antivirus software on that computer? Make sure the PostgreSQL data directory and the backup directory are excluded. Run procmon <http://technet.microsoft.com/en-us/sysinternals/bb896645> to see what copy tries to do when it fails. -- Christian
On Wed, Jan 19, 2011 at 19:20, Christian Ullrich <chris@chrullrich.net> wrote: > * tuanhoanganh wrote: > >> I have checked your solution. > >> - Target disk full : No >> - PostgreSQL user does not have write privilege for the target directory >> : No >> - Target file exists already (then you have a bigger problem) : Last >> file in D:/3SDATABACKUP/PITR/WAL is 00000001000000040000005D > >> - PostgreSQL user does not have full control privileges for the source >> file (the copy command needs them) : i switch to user postgres an copy >> 00000001000000040000005E from source to d:\temp and create new text file >> on D:/3SDATABACKUP/PITR/WAL it is ok. No access denied > > So when PostgreSQL runs "copy 000...5E D:\...", it fails, and when you do > the same thing as the PostgreSQL user, it works. Interesting. Try increasing > the log level in postgresql.conf to see if it logs the error message from > copy, or try xcopy instead of copy. Note thatn when PostgreSQL runs, it will shed any rights given through "Administrators" or "Power Users" group. So this is not an identical test. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On 01/19/11 9:23 AM, tuanhoanganh wrote: > > > 2011-01-06 08:27:54 ICT LOG: archive command failed with exit > code 1 > 2011-01-06 08:27:54 ICT DETAIL: The failed archive command > was: copy > pg_xlog\00000001000000040000005E > D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E > > [... lots more ...] > is D: a network mapped drive? those mappings are per user session specific, and the database service will not see them as it runs in a service session, not on the desktop session.... use a UNC instead if you are going across the network, eg, \\server\share\path\filename
* Magnus Hagander wrote: > On Wed, Jan 19, 2011 at 19:20, Christian Ullrich<chris@chrullrich.net> wrote: >> So when PostgreSQL runs "copy 000...5E D:\...", it fails, and when you do >> the same thing as the PostgreSQL user, it works. Interesting. Try increasing >> the log level in postgresql.conf to see if it logs the error message from >> copy, or try xcopy instead of copy. > > Note thatn when PostgreSQL runs, it will shed any rights given through > "Administrators" or "Power Users" group. So this is not an identical > test. We cannot assume that the one-click installer was used, but if it was, the service account it creates will be a member of the Users group only. tuanhoanganh, what did you download to install Postgres? -- Christian
NO, D is local driver.
I setup PITR from 01/01/2011. It work well from 01->05/01/2011 (D:/3SDATABACKUP/PITR/WAL has 00000001000000040000005D) but error from 06/01/2011
I setup PITR from 01/01/2011. It work well from 01->05/01/2011 (D:/3SDATABACKUP/PITR/WAL has 00000001000000040000005D) but error from 06/01/2011
On Thu, Jan 20, 2011 at 3:56 AM, John R Pierce <pierce@hogranch.com> wrote:
On 01/19/11 9:23 AM, tuanhoanganh wrote:is D: a network mapped drive?
2011-01-06 08:27:54 ICT LOG: archive command failed with exit
code 1
2011-01-06 08:27:54 ICT DETAIL: The failed archive command
was: copy
pg_xlog\00000001000000040000005E
D:/3SDATABACKUP/PITR/WAL/00000001000000040000005E
[... lots more ...]
those mappings are per user session specific, and the database service will not see them as it runs in a service session, not on the desktop session....
use a UNC instead if you are going across the network, eg, \\server\share\path\filename
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I download postgresql from Enterprise DB
On Thu, Jan 20, 2011 at 6:06 AM, Christian Ullrich <chris@chrullrich.net> wrote:
* Magnus Hagander wrote:On Wed, Jan 19, 2011 at 19:20, Christian Ullrich<chris@chrullrich.net> wrote:We cannot assume that the one-click installer was used, but if it was, the service account it creates will be a member of the Users group only.So when PostgreSQL runs "copy 000...5E D:\...", it fails, and when you do
the same thing as the PostgreSQL user, it works. Interesting. Try increasing
the log level in postgresql.conf to see if it logs the error message from
copy, or try xcopy instead of copy.
Note thatn when PostgreSQL runs, it will shed any rights given through
"Administrators" or "Power Users" group. So this is not an identical
test.
tuanhoanganh, what did you download to install Postgres?
--
Christian
* tuanhoanganh wrote: > I download postgresql from Enterprise DB > > On Thu, Jan 20, 2011 at 6:06 AM, Christian Ullrich <chris@chrullrich.net > <mailto:chris@chrullrich.net>> wrote: > We cannot assume that the one-click installer was used, but if it > was, the service account it creates will be a member of the Users > group only. And, to confirm, when you did the copy yourself, you were logged on as the "postgres" user that was created during the installation? Then please recheck that the user the PostgreSQL service is running as does in fact have: - full control permissions for the source directory (data and all subdirs) - full control permissions for the target directory (WAL) - at least "read and execute" permissions for all directories from the D: root down to the PITR directory If you have enabled disk quotas, make sure that the postgres user has not reached the quota limit on the D: drive. Have you had any luck with xcopy instead of copy, or has procmon found out which operation actually fails? -- Christian
* tuanhoanganh wrote: > Here is procmon i thinks error [some procmon events] No, that is all OK. The event at 2:39:55.7588651 is where Postgres starts cmd.exe to perform the copy. The really interesting data would be from cmd.exe itself, which implements the copy command. Please send the events from cmd.exe for the ten seconds following that timestamp. If it is overly much, please send it to me directly. -- Christian
I have changed archive_command to
archive_command = 'copy %p D:\\3SDATABACKUP\\PITR\\WAL\\%f'
and it work again.
But why old archive_command work from 01/01 to 05/01
archive_command = 'copy %p D:/3SDATABACKUP/PITR/WAL/%f'
Thank for your help
Tuan Hoang ANh
archive_command = 'copy %p D:\\3SDATABACKUP\\PITR\\WAL\\%f'
and it work again.
But why old archive_command work from 01/01 to 05/01
archive_command = 'copy %p D:/3SDATABACKUP/PITR/WAL/%f'
Thank for your help
Tuan Hoang ANh
On Fri, Jan 21, 2011 at 8:58 PM, Christian Ullrich <chris@chrullrich.net> wrote:
* tuanhoanganh wrote:[some procmon events]Here is procmon i thinks error
No, that is all OK. The event at 2:39:55.7588651 is where Postgres starts cmd.exe to perform the copy. The really interesting data would be from cmd.exe itself, which implements the copy command.
Please send the events from cmd.exe for the ten seconds following that timestamp. If it is overly much, please send it to me directly.
--
Christian
* tuanhoanganh wrote: > I have changed archive_command to > archive_command = 'copy %p D:\\3SDATABACKUP\\PITR\\WAL\\%f' > and it work again. Argh. How could I not have seen that? > But why old archive_command work from 01/01 to 05/01 > archive_command = 'copy %p D:/3SDATABACKUP/PITR/WAL/%f' It works with the forward slashes if the destination path is quoted, but it looks like you did not do that in any of your examples. Other than that, I have no idea. -- Christian