|
|
F. E. C. O.
|
FECO: |
Postgres#1 |
|
PRODUCT: |
Windows , Postgres 8.2 |
|
PRODUCT WEB SITE: |
N/A |
|
RELEASE DATE: |
|
|
ESTIMATED MAN HOURS: |
N/A |
|
CLASS OF CHANGE: |
INFORMATIONAL |
|
REASON FOR CHANGE: |
|
|
RELATED FECO/AB: |
|
Installing PostgreSQL on Windows for use with Quantum Financials
Throughout this FECO a carriage return is
shown either by the symbol ¿ or <CR>.
A space in a string is shown by the symbol Ñ, all other spaces are
purely cosmetic
PostgreSQL for windows 8.2 (or later)
Administrative User password available
PC / SERVER with Windows 2000 / 2003 / XP
installed
3.1 Login
to the machine as the local administrator (not Domain Administrator). Insert
the CDROM with the PostgreSQL for Windows software on it into the CDROM drive.
3.2 Explore
the
3.3
The first screen displayed welcomes you to the
PostgreSQL installation wizard and
prompts you to select the
language for the installation. The default language is set to English /
English and there is no need to modify this. Select ‘Start’ to
proceed
3.4 You will now be advised
that it is recommended that you exit any programs that are
currently running. Close any
additional active programs and select Next to continue
3.5 The PostgreSQL
installation notes is the next screen to be displayed
Once you have read the
notes select next to continue
3.6 The installation options
screen is now displayed
If you are amending the
path PostgreSQL is installed into you need to do it here. You
should browse to the path
you wish to install into and select OK. It is recommended you install into a
different path e.g c:\postgres. Select Next to continue
3.7 The next screen displayed is the Service
configuration screen. As we want PostgreSQL to start immediately when the
system reboots we will install it as a service.
The Account Domain will be different
for each system (it will be the NetBIOS name of the system) and all you need to
specify is the password to be used for the postgres user. The default password
for the postgres user is postgres. Enter and confirm the password and select
Next.
3.8 The Initialise database cluster screen is
now displayed
Select ‘Accept connections
on all addresses, not just localhost’ , change the encoding to LATIN1 and
enter and confirm the password for the postgres database user of ‘postgres’.
Select Next to continue.
3.9
You will be advised that you need to amend the
pg_hba.conf file to allow connections
from external network
addresses. Select OK
3.10
The Enable procedural language screen is then
displayed
Select Next to
continue
3.11
You will now have the enable contrib modules screen displayed. Select Next to
continue.
3.12
You are now ready to install PostgreSQL, select Next
and the installation will start
3.13
Once the installation is complete, select Finish
and reboot the system.
3.14
Once the server has rebooted you need to configure who
can access the machine. To do this select start, programs, PostgreSQL,
configuration files and edit pg_hba.conf. You need to add a line
in to allow connections from the network you wish to connect from e.g for users
on a network who’s network is 192.168.96.0/24 the line would be
# IPv4 local
connections:
host all all 127.0.0.1/32 md5 (existing line)
host
all all 192.168.96.0/24
md5
If there are multiple networks
connecting to the server, there should be multiple entries in the configuration
file. Do not use 0.0.0.0/0 as an IP address to connect from as this will allow
connections from anywhere. Once the amendment has been done, select File
and save.
3.15 To bring the changes into effect you need to
reload the configuration. To do this select start, programs, PostgreSQL
and reload configuration. The new configuration will then be active.
4 Accessing the PostgreSQL
databases
When PostgreSQL for windows
is installed you have 3 simple ways to access the databases.
4.1
psql
to postgres
To get to the psql to
postgres screen select start, programs, PostgreSQL and psql
to postgres.
This screen gives you a
direct connection into the postgres database. You can execute all of the
PostgreSQL database commands from here with superuser permissions.
4.2
Command
prompt
To get to the command prompt
screen select start, programs, PostgreSQL and command
prompt. This will bring up a dos window and you will be in the bin directory
of where ever your PostgreSQL is installed into.
You can execute any of the
standard psql commands here such as pg_dump, pg_restore and psql.
This can be used to test database connections
4.3
Pgadmin
III
To get to the Pgadmin III
screen select start, programs, PostgreSQL and Pgadmin III.
Pgadmin is a standard tool for accessing PostgreSQL databases on Windows
and Linux. The software can be used to amend tables and permissions using a GUI
interface rather than using the command line in PostgreSQL.
Please refer to the online
documentation for further information (http://www.pgadmin.org/docs)
5
Backing
up the PostgreSQL Databases
5.1
When the Windows server is backed up the PostgreSQL
binaries and databases should also be included within the backup. In the event
of a major system problem the tape backup can then be restored to the system in
it’s entirety. If you require a more selective restore of the data then you
need to dump the databases using pg_dump.
5.2
A batch file should be created and the databases
should be dumped using the pg_dump command to a directory / drive before they are backed up to tape. An example
of a simple batch file is shown below
C:\ Edit Backup.bat
cd c:\postgres\bin
pg_dump -Fc -h localhost -U
postgres -d template1 > c:\postgres\data\template1.sql
pg_dump -Fc -h localhost -U
postgres -d qf1 > c:\postgres\data\qf1.sql
pg_dump -Fc -h localhost -U
postgres -d qf1H > c:\postgres\data\qf1H.sql
In the example shown the
template1,qf1 and qf1H databases are backed up to the data folder in c:\postgres.
This directory could then be included on a full system save and the data would
be backed up. The batch file could be run manually or as a scheduled task. The
batch file should include all databases with the exception of template0.