Postgres 9.1 DEB

Postgres 9.1 DEB

From OpenSCG Wiki

Jump to: navigation, search

OpenSCG provides PostgreSQL 9.1 DEB packages for an easy installation experience on Ubuntu 8+ distributions. This tutorial explains how to install and configure PostgreSQL using OpenSCG's deb packages.

Requirements

Appropriate 32 or 64 bit PostgreSQL 9.1 deb package from OpenSCG.

Installation Steps

The installation of PostgreSQL 9.1 server is very easy. To install:

• Download and save deb package.

user @ubuntu:~$ ls -l postgres_9.1.1- 1.amd64.openscg.deb
-rw-r--r-- 1 user user 16375318 2011-02-02 09: 46 postgres_9.1.1- 1.amd64.openscg.deb

• Install the deb package as sudo

user @ubuntu:~$ sudo dpkg -i . /postgres_9.1.1- 1.amd64.openscg.deb
[sudo] password for user:
Selecting previously deselected package postgres91.
(Reading database ... 118945 files and directories currently installed. )
Unpacking postgres (from ... /postgres_9.1.1- 1.amd64.openscg.deb ) ...
Setting up postgres91 (9.1.1- 1 ) ...
Processing triggers for ureadahead ...
ureadahead will be reprofiled on next reboot
user @ubuntu:~$

PostgreSQL is cleanly installed to the "/opt/postgres/9.1" directory without changing any other shared libraries on your system.   All the libraries that PostgreSQL uses are in the "/opt/postgres/9.1/lib" directory to minimize the chances of conflicts and other incompatibilities.

Configure and initializing PostgreSQL Server

OpenSCG's server control script makes it very easy to configure the PostgreSQL server. The start command will attempt to initialize the server on its first usage (technically in the absence of data directory).

• Enter following command in your shell prompt (as sudo):

user @ubuntu:~$ sudo /etc /init.d /postgres- 9.1-openscg start

• You will be prompted to provide database server port, superuser password and server startup options. You can either press <ENTER> for default values, or you can provide your own customized inputs. Following the inputs, the database cluster will be initialized and server will be started.

user @ubuntu:~$ sudo /etc /init.d /postgres- 9.1-openscg start
PostgreSQL 9.1 Configuration
---------------------------------
This will initialize PostgreSQL database cluster. The following
questions will determine the database server port, superuser password
and to start server on system boot. Press <ENTER > to accept defaults.

Specify PostgreSQL server port [ 5432 ]:


Specify superuser password [password ]:
Do you want PostgreSQL server to be started on boot (y /n ) [y ]:

useradd: warning: the home directory already exists.
Not copying any file from skel directory into it.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF- 8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /opt /postgres / 9.1 /data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 24MB
creating configuration files ... ok
creating template1 database in /opt /postgres / 9.1 /data /base / 1 ... ok
initializing pg_authid ... ok
setting password ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL /pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

Success. You can now start the database server using:

    /opt /postgres / 9.1 /bin /postgres -D /opt /postgres / 9.1 /data
or
    /opt /postgres / 9.1 /bin /pg_ctl -D /opt /postgres / 9.1 /data -l logfile start

Configuring PostgreSQL server startup scripts
Adding system startup for /etc /init.d /postgres- 9.1-openscg ...
   /etc /rc0.d /K15postgres- 9.1-openscg - > .. /init.d /postgres- 9.1-openscg
   /etc /rc1.d /K15postgres- 9.1-openscg - > .. /init.d /postgres- 9.1-openscg
   /etc /rc6.d /K15postgres- 9.1-openscg - > .. /init.d /postgres- 9.1-openscg
   /etc /rc2.d /S85postgres- 9.1-openscg - > .. /init.d /postgres- 9.1-openscg
   /etc /rc3.d /S85postgres- 9.1-openscg - > .. /init.d /postgres- 9.1-openscg
   /etc /rc4.d /S85postgres- 9.1-openscg - > .. /init.d /postgres- 9.1-openscg
   /etc /rc5.d /S85postgres- 9.1-openscg - > .. /init.d /postgres- 9.1-openscg
Starting PostgreSQL 9.1:
waiting for server to start.... done
server started
PostgreSQL 9.1 started successfully
To load this postgres into your environment, source the env file:
. /opt /postgres / 9.1 /pg91-openscg.env

• The server can now be controlled by either using invoke-rc.d command and/or by calling the init script directly:

user @ubuntu:~$ sudo invoke-rc.d postgres- 9.1-openscg status
pg_ctl: server is running (PID: 13260 )
/opt /postgres / 9.1 /bin /postgres "-D" "/opt/postgres/9.1/data"

user @ubuntu:~$ sudo /etc /init.d /postgres- 9.1-openscg status
pg_ctl: server is running (PID: 13260 )
/opt /postgres / 9.1 /bin /postgres "-D" "/opt/postgres/9.1/data"

The server is controlled by postgres operating system user, which will be created (as a locked account) by the configuration script if it does not already exists.

Setting the Environment and Using the database

---

The OpenSCG's deb package does not do anything to your system environment in order to prevent conflicts with other software.  If you wish to load the environment for this install, you can use the included env file:

user @ubuntu:~$ . /opt /postgres / 9.1 /pg91-openscg.env
user @ubuntu:~$ psql
Password:
psql (9.1.1 )
Type "help" for help.

postgres= #

This will load:

* PGHOME
* PGDATA
* PATH
* LD_LIBRARY_PATH
* PGUSER
* PGDATABASE
* PGPORT

Default database

---

The default database created at initialization time (as with most other PostgreSQL packages) is:

   postgres

Default username

---

The default superuser created at initialization time (as with most other PostgreSQL packages) is:

   postgres

Default password

---

The default password (unless you set it at initialization time) is:

   password

Change the default password

---

To change the default password, you first need to login to the database with your current password:

user @ubuntu:~$ . /opt /postgres / 9.1 /pg91-openscg.env
user @ubuntu:~$ psql
Password:
psql (9.1.1 )
Type "help" for help.

postgres= #

Now, change the password for the postgres user:

postgres= # ALTER USER postgres WITH PASSWORD 'newpasswordgoeshere';
ALTER ROLE
postgres= #

Now your password is changed.

Forgot my password!

---

If you've forgotten your password, that's no problem.  Here's the reset process:

• Use sudo or become postgres user. (Note: postgres might be locked, in which case you must use sudo - or unlock postgres account)

• Edit the pg_hba.conf (host based access) file using your favorite editor

user @ubuntu:~$ sudo vi /opt /postgres / 9.1 /data /pg_hba.conf

• Change highlighted line

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5

by changing connection method from md5 to trust:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust

• Save your changes

• Command postgres to re-read it's config file (either using sudo or as postgres user)

user @ubuntu:~$ sudo /etc /init.d /postgres- 9.1-openscg reload
Reloading PostgreSQL 9.1:
server signaled

• Login to the database (you will not be prompted for password).

user @ubuntu:~$ exit
exit
user @ubuntu:~$ . /opt /postgres / 9.1 /pg91-openscg.env
user @ubuntu:~$ psql
psql (9.1.2 )
Type "help" for help.

postgres= #

• Change the password (as described in previous section)

• Edit the pg_hba.conf (host based access) file and change trust back to md5 and reload the server.

Uninstalling PostgreSQL Server

The uninstallation of PostgreSQL server can be performed as follows by using dpkg:

user @ubuntu:~$ sudo dpkg --remove postgres91
(Reading database ... 121647 files and directories currently installed. )
Removing postgres ...
Attempting to stop server...
Stopping PostgreSQL 9.1:
waiting for server to shut down.... done
server stopped
Attempting to update server startup status...
Removing any system startup links for /etc /init.d /postgres- 9.1-openscg ...
   /etc /rc0.d /K15postgres- 9.1-openscg
   /etc /rc1.d /K15postgres- 9.1-openscg
   /etc /rc2.d /S85postgres- 9.1-openscg
   /etc /rc3.d /S85postgres- 9.1-openscg
   /etc /rc4.d /S85postgres- 9.1-openscg
   /etc /rc5.d /S85postgres- 9.1-openscg
   /etc /rc6.d /K15postgres- 9.1-openscg
dpkg: warning: while removing postgres, directory '/opt/postgres/9.1' not empty so not removed.
dpkg: warning: while removing postgres, directory '/opt/postgres' not empty so not removed.
dpkg: warning: while removing postgres, directory '/opt' not empty so not removed.
Processing triggers for ureadahead ...

Note 1: The data folder is not deleted after uninstallation.
Note 2: The dpkg warnings can be safely ignored.

Reporting Bugs/Suggestions

If you have any questions related to deb installation, or you would like to report any bugs and/or suggestions in OpenSCG's deb packaging, feel free to join OpenSCG mailing list to discuss.