Re: BAKUP ISSUE

HI,
I have a problem when i take backup using pg_basebackup and
pg_start_backup('base');

following ,

1)
select pg_start_backup('base');
pg_start_backup
--------------------
0/16000028

2)
postgres=# select pg_stop_backup();
NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to
be ar
chived
WARNING: pg_stop_backup still waiting for all required WAL segments to be
archi
ved (60 seconds elapsed)
HINT: Check that your archive_command is executing properly.
pg_stop_backup ca
n be canceled safely, but the database backup will not be usable without
all the
WAL segments.

postgresql.conf in my postgres
..........................

listen_addresses = '*'

wal_level =hot_standby

# - Archiving -

archive_mode = on
archive_command = 'copy "%p" "C:\\Program
Files\\PostgreSQL\\9.3\\data\\pg_xlog\\%f"'

max_wal_senders =5
wal_keep_segments = 10
auto_vaccum is on

please let me know what i should i do...?

Thanks in advance,
ramesh

On Wed, Jul 9, 2014 at 12:45 PM, Ramesh T <rameshparnanditech(at)gmail(dot)com>
wrote:

> HI,
> I have a problem when i take backup using pg_basebackup and
> pg_start_backup('base');
> following ,
>

What is the problem? You didn't write it. Just some outputs and config, but
you never mentioned what is the problem itself.

> archive_mode = on
> archive_command = 'copy "%p" "C:\\Program
> Files\\PostgreSQL\\9.3\\data\\pg_xlog\\%f"'
>

This archive_command looks bad.

Is c:\program files\postgresql\9.3\data\pg_xlog directory for xlog for THIS
postgresql?
If yes - why would you want to copy xlog back to its original location?
If no - while it might work, I don't think it's safe to copy xlogs directly
to pg_xlog directory of slave (assuming it's slave).

depesz

Hi,

First - do not take the messages off list. Someone else might be able to
help you if you'll mail to the list, I'm not always there, and others might
have better ideas.

On Wed, Jul 9, 2014 at 2:43 PM, Ramesh T <rameshparnanditech(at)gmail(dot)com>
wrote:

> i rune problem is when i run the pg_stop_backup() its return error in
> this way
>
> postgres=# select pg_stop_backup();
>
>
> *NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments
> to be ar*
> *chived*
> *WARNING: pg_stop_backup still waiting for all required WAL segments to
> be archi*
> *ved (60 seconds elapsed)*
> *HINT: Check that your archive_command is executing properly.
> pg_stop_backup ca*
> *n be canceled safely, but the database backup will not be usable without
> all the*
> * WAL segments.*
>

But there is no ERROR?! What error are you talking about?

This is just notice and a warning.

> i changed in postgresql.conf to archive_command = 'copy "%p" "C:\Program
> Files\PostgreSQL\ramesh %f"' still working same error return..
>

What error? So far you didn't show any.

Is the archiving working? Are there files in c:\program
files\postgresql\ramesh ?
If not, what does pg logs say about archiving? Any errors there?

depesz

hi,
Yes,not an error it is a warning and archive is working.
when i archivelog is on in postgresql.conf .

when try to run below statement its return warning and HINT: Check that
your archive_command is executing properly

like below ..
postgres=# select pg_stop_backup();

it is going on 60 seconds,120 seconds 240 seconds,480 seconds ....and so
when it is going stop and gets completed.

it is taking too much of time but not completing,what i should i do..

*NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to
be ar*
*chived*
*WARNING: pg_stop_backup still waiting for all required WAL segments to be
archi*
*ved (60 seconds elapsed)*
*HINT: Check that your archive_command is executing properly.
pg_stop_backup ca*
*n be canceled safely, but the database backup will not be usable without
all the*
* WAL segments.*

*WARNING: pg_stop_backup still waiting for all required WAL segments to
be archi*
*ved (120 seconds elapsed)*
*HINT: Check that your archive_command is executing properly.
pg_stop_backup ca*
*n be canceled safely, but the database backup will not be usable without
all the*
* WAL segments.*

*i changed path *c:\program files\postgresql\9.3\data\pg_xlog TO c:\program
files\postgresql\ramesh
no files in c:\program files\postgresql\ramesh

in pg log it say

2014-07-09 18:53:33 IST LOG: archive command failed with exit code 1
2014-07-09 18:53:33 IST DETAIL: The failed archive command was: copy
"pg_xlog\000000010000000000000001" "C:Program FilesPostgreSQL
amesh 000000010000000000000001"
2014-07-09 18:53:34 IST LOG: archive command failed with exit code 1
2014-07-09 18:53:34 IST DETAIL: The failed archive command was: copy
"pg_xlog\000000010000000000000001" "C:Program FilesPostgreSQL
amesh 000000010000000000000001"
2014-07-09 18:53:34 IST WARNING: archiving transaction log file
"000000010000000000000001" failed too many times, will try again later

please let me know what should i do,how to set the archive command in
windows.

thanks in advance
ramesh

On Wed, Jul 9, 2014 at 6:23 PM, hubert depesz lubaczewski <depesz(at)gmail(dot)com>
wrote:

> Hi,
>
> First - do not take the messages off list. Someone else might be able to
> help you if you'll mail to the list, I'm not always there, and others might
> have better ideas.
>
>
> On Wed, Jul 9, 2014 at 2:43 PM, Ramesh T <rameshparnanditech(at)gmail(dot)com>
> wrote:
>
>> i rune problem is when i run the pg_stop_backup() its return error in
>> this way
>>
>> postgres=# select pg_stop_backup();
>>
>>
>> *NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments
>> to be ar*
>> *chived*
>> *WARNING: pg_stop_backup still waiting for all required WAL segments to
>> be archi*
>> *ved (60 seconds elapsed)*
>> *HINT: Check that your archive_command is executing properly.
>> pg_stop_backup ca*
>> *n be canceled safely, but the database backup will not be usable without
>> all the*
>> * WAL segments.*
>>
>
> But there is no ERROR?! What error are you talking about?
>
> This is just notice and a warning.
>
>
>> i changed in postgresql.conf to archive_command = 'copy "%p" "C:\Program
>> Files\PostgreSQL\ramesh %f"' still working same error return..
>>
>
> What error? So far you didn't show any.
>
> Is the archiving working? Are there files in c:\program
> files\postgresql\ramesh ?
> If not, what does pg logs say about archiving? Any errors there?
>
> depesz
>

Hi,

On Wed, Jul 9, 2014 at 2:43 PM, Ramesh T <rameshparnanditech(at)gmail(dot)com>
> wrote:
>
>> i rune problem is when i run the pg_stop_backup() its return error in
>> this way
>>
>> postgres=# select pg_stop_backup();
>>
>>
>> *NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments
>> to be ar*
>> *chived*
>> *WARNING: pg_stop_backup still waiting for all required WAL segments to
>> be archi*
>> *ved (60 seconds elapsed)*
>> *HINT: Check that your archive_command is executing properly.
>> pg_stop_backup ca*
>> *n be canceled safely, but the database backup will not be usable without
>> all the*
>> * WAL segments.*
>>
> i changed in postgresql.conf to archive_command = 'copy "%p" "C:\Program
>> Files\PostgreSQL\ramesh %f"' still working same error return..
>>
>
I believe, you have to give the archive_command in windows like below, due
to window's escape sequences behavior.

'copy "%p" "C:\\\\Program Files\\\\PostgreSQL\\\\<Archive Location, where
postgreSQL Instance owner has the permission>\\\\%f " '

Regards,
Dinesh
manojadinesh.blogspot.com

On Wed, Jul 9, 2014 at 3:28 PM, Ramesh T <rameshparnanditech(at)gmail(dot)com>
wrote:

> Yes,not an error it is a warning and archive is working.
>

How can you say that archive is working when in logs you have?

> 2014-07-09 18:53:33 IST LOG: archive command failed with exit code 1
> 2014-07-09 18:53:33 IST DETAIL: The failed archive command was: copy
> "pg_xlog\000000010000000000000001" "C:Program FilesPostgreSQL
> amesh 000000010000000000000001"
> 2014-07-09 18:53:34 IST LOG: archive command failed with exit code 1
> 2014-07-09 18:53:34 IST DETAIL: The failed archive command was: copy
> "pg_xlog\000000010000000000000001" "C:Program FilesPostgreSQL
> amesh 000000010000000000000001"
> 2014-07-09 18:53:34 IST WARNING: archiving transaction log file
> "000000010000000000000001" failed too many times, will try again later
>

Fix the archive command so that it will actually work (files should appear
in destination directory).

I have 0 knowledge about windows, but I would guess you need to use \\ and
not \ in the path. Also - does the destination path exist?

depesz

On Wed, Jul 9, 2014 at 10:54 PM, hubert depesz lubaczewski <depesz(at)gmail(dot)com
> wrote:

> I have 0 knowledge about windows, but I would guess you need to use \\ and
> not \ in the path. Also - does the destination path exist?
>
Confirmed.
--
Michael

De : pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] De la part de hubert depesz lubaczewski
Envoyé : July-09-14 9:55 AM

On Wed, Jul 9, 2014 at 3:28 PM, Ramesh T <rameshparnanditech(at)gmail(dot)com<mailto:rameshparnanditech(at)gmail(dot)com>> wrote:
Yes,not an error it is a warning and archive is working.

How can you say that archive is working when in logs you have?

2014-07-09 18:53:33 IST LOG: archive command failed with exit code 1
2014-07-09 18:53:33 IST DETAIL: The failed archive command was: copy "pg_xlog\000000010000000000000001" "C:Program FilesPostgreSQL
amesh 000000010000000000000001"
2014-07-09 18:53:34 IST LOG: archive command failed with exit code 1
2014-07-09 18:53:34 IST DETAIL: The failed archive command was: copy "pg_xlog\000000010000000000000001" "C:Program FilesPostgreSQL
amesh 000000010000000000000001"
2014-07-09 18:53:34 IST WARNING: archiving transaction log file "000000010000000000000001" failed too many times, will try again later

Fix the archive command so that it will actually work (files should appear in destination directory).
I have 0 knowledge about windows, but I would guess you need to use \\ and not \ in the path. Also - does the destination path exist?
depesz

Also, on windows, one can use the forward slash ‘/’ instead of the backslash in a path
Just my 2 cents.
-Mark