Monday, July 20, 2009

Start a brand new PostgreSQL server

Create a directory to hold the postgres database cluster files. (Eg. c:\pgdata or /usr/local/pgsql/pgdata in linux)

Cluster is a collection of databases that are managed by a single server instance.
Be sure the pgdata directory owned by postgres user and the directory is empty

Type the following command to initialise the cluster.
initdb -D c:\pgdata -U postgres

Start the cluster by invoking the following command.
pg_ctl -D c:\pgdata -l c:\pgdata\logfile start

To stop the database cluster use the following command.
pg_ctl -D c:\pgdata stop

Other Info

Create a user called "user1"
createuser -U postgres -P user1 (Prompted for a password)

Create a database called "database1"
createdb -U user1 -E UTF-8 database1

Create Language
createlang -U postgres -d database1 plpgsql

Exporting data from a database.
pg_dump -U user1 database1 > dumpfile.dmp

Importing data to the database
psql -U user1 database1 < dumpfile.dmp

Check the status and data file location:
ps aux | grep post

Default data file location: /var/lib/postgresql/

Change default password ALTER USER postgres WITH PASSWORD ‘password’;
change the Linux user password of postgres to the same.

(Note: "postgres" is the user created when installing Postgre. If you create a user account named postgres in the OS and installed Postgre into this account "postgres" can be ommited. This is valid for both Windows OS and Linux. )

No comments:

Post a Comment