Tutorial on installing and configuring PostgreSQL database instances in Debian

I used MySQL and wanted to try PostgreSQL. It is said that it is also very powerful. I searched the internet for related Chinese books. Unfortunately, there are far fewer books than MySQL, however, I found documents translated by volunteers on the official website (refer to the 9.1 document translation project). To be honest, I still have a hard time reading English documents. Therefore, Chinese documents are preferred.

In Debian, you can run the apt-get command to directly install the tool:

Sudo apt-get install postgresql-client postgresql-server-dev-all

After the installation is complete, PostgreSQL creates a user named postgres by default. This is the same as the root account of MySQL and the sa account of SQL Server, which is a super administrator account. Unlike MySQL, it also created a Unix system account of postgres. Like the master database of SQL Server, PostgreSQL's default database is template1. You can use the command line management tool psql to manage it, of course, the premise is to switch to the postgres system account:

# Su-postgres
$ Psql

Or you can use sudo to run psql as postgres in one step, saving you the trouble of switching:

Sudo su IPVs-c psql template1

After the preceding commands are completed, you can see the database operation command interface. Note that q is the exit command, and each SQL statement ends with a semicolon. If you want to connect to the specified database, use the command in the format of c databasename.

Well, let's enter q to exit the command interface first. Then we need to create a common account, because we do not recommend that you use the Super Administrator's postgres account to operate our own database.

First, create a system account named mypguser.

Sudo adduser mypguser

Use S s to connect to template1 and enter the psql command interface:

Sudo su IPVs-c psql template1

Create a new database user and a new database, and grant all permissions to the new database:

S = # create user mypguser with password 'mypguserpass ';
Postgres = # create database mypgdatabase;
Postgres = # grant all privileges on database mypgdatabase to mypguser;

Of course, the above operations can be implemented by using the shell command createuser mypguser and createdb mypgdatabase respectively:

# Createuser mypguser # from regular shell
# Su-mypguser
$ Psql postgres
Postgres = # create database mypgdatabase;

After completing these steps, run the q command to exit the psql console.

Postgres = # q

Now we can use the specified account to manage our database:

Sudo su mypguser-c 'psql-d mypgdatabase'

Wait. The most important step is to reset the postgres account password. Run the following command:

Sudo su IPVs-c psql template1
Template1 = # alter user login s with password 'yourpassword'
Template1 = # q

Do not forget the password for the system account:

Sudo passwd-d postgres

Now, the simple installation and account creation have been completed, and some possible errors are introduced:

If the following error occurs:

Psql: FATAL: Ident authentication failed for user "mypguser"

Edit your pg_hba.conf file. This file is generally located in/etc/postgresql/X.Y/main/pg_hba.conf. X.Y is the version number of your PostgreSQL. Change peer in the following line to trust:

Local all trust # replace ident or peer with trust

If the following error occurs:

Psql: FATAL: Peer authentication failed for user "mypguser"

Please still modify the pg_cmd.conf file. The peer in the following line is md5:

Local all md5 # replace peer with md5

After completing the preceding modification, reload postgresql:

/Etc/init. d/postgresql reload