[GENERAL] WAL archiving to network drive
I'm setting up WAL archiving on a Windows machine & need to copy the WAL files to a network drive. Is it best to give the 'postgres' user network access & archive the WAL files directly to the network drive? Or archive the WAL files to a local folder and then use a scheduled task to move them to the network drive? (Or something else entirely?) Thanks, --Rob Adams -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] recovery via base + WAL replay failure
Finally figured out what was wrong. The data folder had incorrect permissions after unzipping the base backup. For me, the solution was unchecking the "Inherit from parent the permission entries that apply to child objects" option in the Advanced Security Settings dialog for the data folder & giving the postgres user full control. Nothing appeared in the log when the database failed to startup b/c the permissions were wrong. However, an application error did get reported to Windows which I found using the Event Viewer. --Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] recovery via base + WAL replay failure
I found error log entries in the Windows Event Viewer:
2008-08-01 23:57:55 GMT FATAL: could not remove old lock file
"postmaster.pid": Permission denied
2008-08-01 23:57:55 GMT HINT: The file seems accidentally left over,
but it could not be removed. Please remove the file by hand and try again.
However, there is no postmaster.pid file in the data directory. (I
can't find one anywhere else, either.)
Any ideas?
Thanks,
--Rob Adams
Lennin Caro wrote:
what error show the log file?
--- On Mon, 8/4/08, Greg Smith <[EMAIL PROTECTED]> wrote:
From: Greg Smith <[EMAIL PROTECTED]>
Subject: Re: [GENERAL] recovery via base + WAL replay failure
To: "Rob Adams" <[EMAIL PROTECTED]>
Cc: "postgres general"
Date: Monday, August 4, 2008, 5:58 PM
On Sun, 3 Aug 2008, Rob Adams wrote:
I made a base backup while the postgres was running
using the following batch
file:
psql -d test_database -U user_name -c "SELECT
pg_start_backup('test');"
What did you have archive_command set to? That needs to
dump the WAL
files generated while the backup is going on somewhere that
gets copied
over after the main copy is done, and you need the last of
them referenced
by the backup copied over before you can use that backup.
Steps (1) and
(5) of
http://www.postgresql.org/docs/current/static/continuous-archiving.html
are the hard parts here and I don't see that you're
addressing them so
far, and that will keep the copy from starting if all the
files aren't
there.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com
Baltimore, MD
--
Sent via pgsql-general mailing list
(pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] recovery via base + WAL replay failure
There is nothing in the log file (in pg_log dir) with regard to this.
Should I set any particular parameter in the postgresql.conf file to log
information about a failed startup? I have not altered or uncommented
any lines in the "ERROR REPORTING AND LOGGING" section of the conf file.
Thanks again,
--Rob Adams
Lennin Caro wrote:
what error show the log file?
--- On Mon, 8/4/08, Greg Smith <[EMAIL PROTECTED]> wrote:
From: Greg Smith <[EMAIL PROTECTED]>
Subject: Re: [GENERAL] recovery via base + WAL replay failure
To: "Rob Adams" <[EMAIL PROTECTED]>
Cc: "postgres general"
Date: Monday, August 4, 2008, 5:58 PM
On Sun, 3 Aug 2008, Rob Adams wrote:
I made a base backup while the postgres was running
using the following batch
file:
psql -d test_database -U user_name -c "SELECT
pg_start_backup('test');"
What did you have archive_command set to? That needs to
dump the WAL
files generated while the backup is going on somewhere that
gets copied
over after the main copy is done, and you need the last of
them referenced
by the backup copied over before you can use that backup.
Steps (1) and
(5) of
http://www.postgresql.org/docs/current/static/continuous-archiving.html
are the hard parts here and I don't see that you're
addressing them so
far, and that will keep the copy from starting if all the
files aren't
there.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com
Baltimore, MD
--
Sent via pgsql-general mailing list
(pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] recovery via base + WAL replay failure
The WAL file archiving appears to be working correctly. These are the
settings I'm using for archiving the WAL files:
archive_mode = on
archive_command = 'copy %p C:\backup\%f /A'
archive_timeout = 15s
Thanks again,
--Rob
Greg Smith wrote:
On Sun, 3 Aug 2008, Rob Adams wrote:
I made a base backup while the postgres was running using the
following batch file:
psql -d test_database -U user_name -c "SELECT pg_start_backup('test');"
What did you have archive_command set to? That needs to dump the WAL
files generated while the backup is going on somewhere that gets copied
over after the main copy is done, and you need the last of them
referenced by the backup copied over before you can use that backup.
Steps (1) and (5) of
http://www.postgresql.org/docs/current/static/continuous-archiving.html
are the hard parts here and I don't see that you're addressing them so
far, and that will keep the copy from starting if all the files aren't
there.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] recovery via base + WAL replay failure
I'm trying to demonstrate recovery using the "continuous archiving"
backup technique. I'm using 8.3 on Windows.
I made a base backup while the postgres was running using the following
batch file:
--
psql -d test_database -U user_name -c "SELECT pg_start_backup('test');"
7za a -tzip "C:\backup\base.zip" "C:\Program Files\PostgreSQL\8.3\data"
psql -d test_database -U user_name -c "SELECT pg_stop_backup();"
--
No indication of any errors. However, I am unable to recover. Here are
my steps:
1. Stop service, replace the data directory w/ the base backup.
2. Create recovery.conf with this setting:
restore_command = 'copy C:\backup\%f "%p"'
3. Try to start service
After about 90 seconds, the dos prompt displays:
"(postgres 8.3) service could not be started"
"The service did not report an error"
Does anyone know what I am probably doing wrong?
Thanks,
--Rob Adams
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] archive_timeout, checkpoint_timeout
I was referring to this post: http://archives.postgresql.org/pgsql-hackers/2007-10/msg01361.php The test database was completely idle. WAL files were only being archived at the interval specified in checkpoint_timeout (I was using the default value) -- archive_timeout didn't make them happen any faster. Upon retesting, archive_timeout is working properly when there are regular updates happening to the database. Thanks for your help! --Rob Adams Tom Lane wrote: Rob Adams <[EMAIL PROTECTED]> writes: archive_timeout only seems to work if it's >= checkpoint_timeout. Hmm, no, they should be pretty independent. Define "seems to work" please? One possible connection is that an xlog file switch will not actually happen unless some xlog output has been generated since the last switch. If you were watching an otherwise-idle system then maybe the checkpoint records are needed to make it look like a switch is needed. OTOH if it's *that* idle then the checkpoints should be no-ops too. So we need a bit more context to understand what's happening. How often do real updates happen on your database? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] archive_timeout, checkpoint_timeout
We want to use an archive_timeout of ~1min to minimize data loss in the event of hardware failure (archive script includes external b/u). archive_timeout only seems to work if it's >= checkpoint_timeout. Could someone please explain in layman's terms the implications of using a checkpoint_timeout of ~1min as well? Is it a bad idea? We use PostgreSQL 8.3 on Windows. Thanks, Rob Adams -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PITR base backup -- stop server or not?
The docs for Making a Base Backup (tar) say that it can be done live without stopping the server: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP (step #3) However, the docs for straight File System Level Backup (tar) say the server must be shut down: http://www.postgresql.org/docs/8.3/interactive/backup-file.html (restriction #1) Is this because replaying the WAL files will fix any of the issues listed in the File System Level Backup restriction #1? Thanks, Rob Adams -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
