Thread: Automated Backup On Windows
FYI on the witting a batch script to run on Windows 2003 R2 server with no install of postgresql or pgadmin III
This will create a new file every time the backup is run with the Name then the date followed with the extension .backup
I would have added it to the postgresqldocs.org webstie but can't create an account for my self. Tried following theses instructions but never could get it to work
I hope these instructions help people...
Step One
Go to Server create a Directory called Drive:\PostgresqlBack
then create a sub directory called "bin" in the Drive:\PostgresqlBack
Step Two
instead of compiling pg_dump.exe used the pgadmin install on the my client and copied the files to the bin folder on the Server
the files required when using pg_dump.exe from PgAdmin install are as follows and must be located in the bin folder
comerr32.dll
gssapi32.dll
k5sprt32.dll
krb_32.dll
libeay32.dll
libiconv2.dll
libpq.dll
Microsoft.VC80.CRT.manifest
msvcm80.dll
msvcp80.dll
msvcr80.dll
pg_dump.dll
ssleay32.dll
zlib1.dll
Step Three
Next Create batch file called something i used postgresqlBackup.bat. The file must be located in PostgresqlBack directory not the bin folder.
Step Four
Open the File then Copy/Paste the following
@echo off
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)
set datestr=%month%_%day%_%year%
echo datestr is %datestr%
set BACKUP_FILE=<NameOfTheFile>_%datestr%.backup
set logFile=EmproDatabase_%datestr%.log
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=<PassWord>
echo on
bin\pg_dump -i -h <HostName> -p 5432 -U <UserName> -F c -b -v -f %BACKUP_FILE% <DATABASENAME>
Step Five
Change <NameOfTheFile> to something. i choose to use the name the name of the database. (make sure the is no spaces after the word BACKUP_FILE any spaces will cause this setting not to work.) Setting is the first part of the file name then followed by the date the file was created with the extension .backup
Change the <PassWord > setting above to the correct password for the backup users. (make sure the is no spaces after the word PGPASSWORD any spaces will cause this setting not to work.)
Note severial tries to get PGPASS file to work on windows never could so decided to use PGPASSWORD setting in the script
Change <HostName> either to ip address or dns name of the server hosting Postgresql.
Change <UserName> to backup user make sure this users has access to database for backup purposes
Change <DATABASENAME> to the database name being backed up.
Save the File
Step Six
Create Task for th Task Scheduler link to MS instructions
Step Seven
Once you have chosen the security context the Task in going to run in, it is advised to change the directory security where the back is in run in and the files are stored so only the backup users can get to it sense it has the high level user name and password stored in plain text.
Step Eight
Go drink beer and something salty. :-)
This will create a new file every time the backup is run with the Name then the date followed with the extension .backup
I would have added it to the postgresqldocs.org webstie but can't create an account for my self. Tried following theses instructions but never could get it to work
I hope these instructions help people...
Step One
Go to Server create a Directory called Drive:\PostgresqlBack
then create a sub directory called "bin" in the Drive:\PostgresqlBack
Step Two
instead of compiling pg_dump.exe used the pgadmin install on the my client and copied the files to the bin folder on the Server
the files required when using pg_dump.exe from PgAdmin install are as follows and must be located in the bin folder
comerr32.dll
gssapi32.dll
k5sprt32.dll
krb_32.dll
libeay32.dll
libiconv2.dll
libpq.dll
Microsoft.VC80.CRT.manifest
msvcm80.dll
msvcp80.dll
msvcr80.dll
pg_dump.dll
ssleay32.dll
zlib1.dll
Step Three
Next Create batch file called something i used postgresqlBackup.bat. The file must be located in PostgresqlBack directory not the bin folder.
Step Four
Open the File then Copy/Paste the following
@echo off
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)
set datestr=%month%_%day%_%year%
echo datestr is %datestr%
set BACKUP_FILE=<NameOfTheFile>_%datestr%.backup
set logFile=EmproDatabase_%datestr%.log
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=<PassWord>
echo on
bin\pg_dump -i -h <HostName> -p 5432 -U <UserName> -F c -b -v -f %BACKUP_FILE% <DATABASENAME>
Step Five
Change <NameOfTheFile> to something. i choose to use the name the name of the database. (make sure the is no spaces after the word BACKUP_FILE any spaces will cause this setting not to work.) Setting is the first part of the file name then followed by the date the file was created with the extension .backup
Change the <PassWord > setting above to the correct password for the backup users. (make sure the is no spaces after the word PGPASSWORD any spaces will cause this setting not to work.)
Note severial tries to get PGPASS file to work on windows never could so decided to use PGPASSWORD setting in the script
Change <HostName> either to ip address or dns name of the server hosting Postgresql.
Change <UserName> to backup user make sure this users has access to database for backup purposes
Change <DATABASENAME> to the database name being backed up.
Save the File
Step Six
Create Task for th Task Scheduler link to MS instructions
Step Seven
Once you have chosen the security context the Task in going to run in, it is advised to change the directory security where the back is in run in and the files are stored so only the backup users can get to it sense it has the high level user name and password stored in plain text.
Step Eight
Go drink beer and something salty. :-)
On Mon, 8 Sep 2008, justin wrote: > I would have added it to the postgresqldocs.org webstie but can't create an > account for my self. Tried following theses instructions > <http://www.postgresqldocs.org/wiki/Automated_Backup_on_Windows> but never > could get it to work That page has been moved to http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows and you shouldn't have a problem getting an account there. The postgresqldocs site ended up being temporary and shutdown once the official Wiki was available. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith wrote: > On Mon, 8 Sep 2008, justin wrote: > >> I would have added it to the postgresqldocs.org webstie but can't >> create an account for my self. Tried following theses instructions >> <http://www.postgresqldocs.org/wiki/Automated_Backup_on_Windows> but >> never could get it to work > > That page has been moved to > http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows and you > shouldn't have a problem getting an account there. The postgresqldocs > site ended up being temporary and shutdown once the official Wiki was > available. > Well I still can't create an account all it says is Login no create account button or screen anywhere?????
On Tue, Sep 9, 2008 at 1:32 PM, justin <justin@emproshunts.com> wrote: > > > Greg Smith wrote: >> >> On Mon, 8 Sep 2008, justin wrote: >> >>> I would have added it to the postgresqldocs.org webstie but can't create >>> an account for my self. Tried following theses instructions >>> <http://www.postgresqldocs.org/wiki/Automated_Backup_on_Windows> but never >>> could get it to work >> >> That page has been moved to >> http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows and you >> shouldn't have a problem getting an account there. The postgresqldocs site >> ended up being temporary and shutdown once the official Wiki was available. >> > Well I still can't create an account all it says is Login no create account > button or screen anywhere????? See the bottom section of the front page: http://wiki.postgresql.org/ -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page wrote:
It might actual help if i read the page :-[ . I kept looking for CREATE ACCOUNT next to login not at the bottom of the page.On Tue, Sep 9, 2008 at 1:32 PM, justin <justin@emproshunts.com> wrote:Greg Smith wrote:On Mon, 8 Sep 2008, justin wrote:I would have added it to the postgresqldocs.org webstie but can't create an account for my self. Tried following theses instructions <http://www.postgresqldocs.org/wiki/Automated_Backup_on_Windows> but never could get it to workThat page has been moved to http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows and you shouldn't have a problem getting an account there. The postgresqldocs site ended up being temporary and shutdown once the official Wiki was available.Well I still can't create an account all it says is Login no create account button or screen anywhere?????See the bottom section of the front page: http://wiki.postgresql.org/
how does this page look to you guys/gals. I have never added anything to a WIKI before so any comments?? I left the original author stuff untouched my edit is appended to the top
sorry forgot the link http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows justin wrote: > how does this page look to you guys/gals. I have never added anything > to a WIKI before so any comments?? I left the original author stuff > untouched my edit is appended to the top >
On Tue, Sep 9, 2008 at 2:12 PM, justin <justin@emproshunts.com> wrote: > how does this page look to you guys/gals. I have never added anything to a > WIKI before so any comments?? I left the original author stuff untouched my > edit is appended to the top > Looks tidy enough to me :-). PgAdmin should be pgAdmin though, but that's just me being picky :-p -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
justin wrote: > how does this page look to you guys/gals. I have never added anything > to a WIKI before so any comments?? I left the original author stuff > untouched my edit is appended to the top Please use subsections to separate both methods. Also it'd be good to mention that PGPASSWORD and .pgpass work with either method ... How about adding the page to the Windows category? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Tue, 9 Sep 2008, Alvaro Herrera wrote: > Please use subsections to separate both methods. > How about adding the page to the Windows category? I just fixed both those. Justin, you might want to look at how I reformatted that to get an idea what the usual style is like. Also, people who write whole articles or large sections are sometimes credited there, you might want to add yourself to the author list for that page at http://wiki.postgresql.org/wiki/Database_Administration_and_Maintenance > Also it'd be good to mention that PGPASSWORD and .pgpass work with > either method ... I put a stub page in at http://wiki.postgresql.org/wiki/Pgpass to cover this whole area but never really filled it in. I'd prefer seeing that get fleshed out and then the automation page can just link to it, because this is a very FAQ. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>> Please use subsections to separate both methods. >> How about adding the page to the Windows category? > > I just fixed both those. Justin, you might want to look at how I > reformatted that to get an idea what the usual style is like. Also, > people who write whole articles or large sections are sometimes > credited there, you might want to add yourself to the author list for > that page at > http://wiki.postgresql.org/wiki/Database_Administration_and_Maintenance Thanks i was going to take this Alvaro suggestion and clean it up more. > >> Also it'd be good to mention that PGPASSWORD and .pgpass work with >> either method ... > > I put a stub page in at http://wiki.postgresql.org/wiki/Pgpass to > cover this whole area but never really filled it in. I'd prefer > seeing that get fleshed out and then the automation page can just link > to it, because this is a very FAQ. I've been trying to figure out how to get pgpass to work on windows with no luck as of yet. If i ever figure out and test pgpass on several other windows version then i'll do up a documentation on pgpass. There is some conflicting instructions on pgpass.
Hi All,
I am getting an error on "GET DIAGNOSTICS " statement in a PL/SQL function
below.
CREATE OR REPLACE FUNCTION test(lid int, OUT nid int) AS $$
DECLARE
road_row road%ROWTYPE;
BEGIN
SELECT * INTO road_row FROM road WHERE link_id=lid;
GET DIAGNOSTICS xcount = ROW_COUNT;
IF xcount = 0 THEN
RAISE DEBUG 'LID=% not found', lid;
nid := -99;
RETURN
END IF
......
The exact error message is:
ERROR: syntax error at or near "xcount" at character
I am must be doing something wrong somewhere.
Thanks a lot for any help.
_C_
<cyw@dls.net> writes:
> I am getting an error on "GET DIAGNOSTICS " statement in a PL/SQL function
> below.
> CREATE OR REPLACE FUNCTION test(lid int, OUT nid int) AS $$
> DECLARE
> road_row road%ROWTYPE;
> BEGIN
> SELECT * INTO road_row FROM road WHERE link_id=lid;
> GET DIAGNOSTICS xcount = ROW_COUNT;
You need to have declared xcount as an integer variable.
> The exact error message is:
> ERROR: syntax error at or near "xcount" at character
Hmm, not the most friendly error, I agree ...
regards, tom lane