Installation and configuration of Postgresql-8.4 in Ubuntu

Step 1: Install Postgresql in Ubuntu

Sudo apt-get install postgresql-8.4 postgresql-client-8.4 postgresql-contrib-8.4

Run the preceding command to install psql on the server and command line client.

/Usr/lib/postgresql/8.4/Stores postgresql-related binary files

/Usr/lib/postgresql/8.4/bin/Executable File

/Usr/lib/postgresql/8.4/lib/Shared Library File

/Etc/postgres/8.4/main/stores the postgresql configuration file

/Var/lib/postgresql/postgres user's main folder

Step 2: Modify the default PostgreSQL database user's postgres Password
By default, PostgreSQL data creates an postgres account and user as the database administrator. The password is random, so:

First, we need to reset the password of the "s" user.

The command line is as follows:

Sudo-u postgres psql (or sudo su postgres-c psql)-> RUN psql, psql is a standard postgressql Client

S = # alter user Login s with password 'your s';-> modify postgres's PASSWORD to S. Do not forget to add a semicolon (the SQL statement before the semicolon will be executed after you press Enter)

S = # \ q-> exit

After changing the password in the database, we also need to modify the "postgres" password for the linux User: Set it to the same password as the postgres account in the database, that is, postgres.

Sudo passwd-d postgres ---> Delete Password

Sudo-u postgres passwd (or sudo su postgres-c passwd) --> Create a password

Enter the same password as before.

Now, we can use the postgres account on the database server to operate the database through clients such as psql or pgAdmin (remote access is not yet available ).

Step 3: Modify the PostgreSQL database configuration for remote access

First, edit postgresql. conf:

Sudo gedit/etc/postgresql/8.4/main/postgresql. conf

Now, we need to modify the "connection and permission" lines.

Change the row: # listen_addresses = 'localhost' to listen_addresses = '*'

Change row: # Change password_encryption = on to password_encryption = on

Save and close gedit.

In the last step, we must set who can operate the data server. This step is completed in pg_hba.conf.

Sudo gedit/etc/postgresql/8.4/main/pg_assist.conf

Add the following content to the bottom of pg_mirror.conf:

# To allow your client visiting postgresql server

Host all 0.0.0.0 0.0.0.0 md5

Explain the last line:

Host indicates that the allowed type is host;

The first all is the allowed database name;

The second all is the permitted user;

The first 0.0.0.0 is the ip address that can be accessed;

The second 0.0.0.0 is the subnet mask that can be accessed;

The final md5 indicates the encryption method of the password. If you change md5 to trust, you do not need to provide the password for the specified range of host databases.

For ip address and subnet mask, you can also modify the ip address (such as 10.13.19.53) and subnet mask (such as 255.255.255.255) of your machine so that only your host can remotely access the database. If you want to use an IP address range, you only need to set the subnet mask to an appropriate value. If the subnet mask is set to 0.0.0.0, all hosts can access the database (the IP address can be set as needed ), if you change the md5 value to trust, you do not need to provide a password when accessing the specified database from a specified range of hosts.

Restart the server, the above configuration takes effect: sudo/etc/init. d/postgresql-8.4 restart

Step 4: create a user and a database.
Use the command line to create users and databases:

Sudo-u postgres createuser-D-P mynewuser -->-D this user does not have the right to create a database.-P prompts you to enter the password. All the following options can be omitted, when the command is executed, the user is prompted to select yes or no.

Sudo-u postgres createdb-O mynewuser mydatabase->-O: set the owner to mynewuser.

Or use psql to create users and databases:

Use psql to log on to the postgresql server: sudo-u postgres psql [-U postgres-h 127.0.0.1] --> RUN psql. The content in [] is optional and used to log on to the server, log on to the local machine by default;

Create a user and a database in the psql program: the user name and database name are case-sensitive after quotation marks are added. Otherwise, the user name and database name are automatically converted to lower case:

Create user "mynewuser" with password 'mynewuser nocreatedb;

Create database "mydatabase" with owner = mynewuser;

Step 5: install and use the pgAdmin3 client to operate a postgresql database

First, install the image client pgAdmin3 and run the command: sudo apt-get install pgadmin3.

Then run the client and connect to the database, directly: