Debian PostgreSQL database installation configuration instance

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:

Copy codeThe Code is as follows:
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:

Copy codeThe Code is as follows:
# Su-postgres
$ Psql

Or you can use sudo to run psql as postgres in one step, saving you the trouble of switching:
Copy codeThe Code is as follows: 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 like \ c databasename.

Well, Let's enter \ q to exit the command interface first. Next we need to create a common account, because we do not recommend using the super administrator's postgres account to operate our own database.

First, create a system account named mypguser.
Copy codeThe Code is as follows: sudo adduser mypguser
Use S to connect to template1 and enter the psql command interface:
Copy codeThe Code is as follows: sudo su ipvs-c psql template1
Create a new database user and a new database, and grant all permissions to the new database:
Copy codeThe Code is as follows: 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:
Copy codeThe Code is as follows:
# 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.
Copy codeThe Code is as follows: S =#\ q
Now we can use the specified account to manage our database:
Copy codeThe Code is as follows:
Sudo su mypguser-c 'psql-d mypgdatabase'

Wait. The most important step is to reset the postgres account password. Run the following command:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows: 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:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows: local all trust # replace ident or peer with trust
If the following error occurs:
Copy codeThe Code is as follows:
Psql: FATAL: Peer authentication failed for user "mypguser"

Please still modify the pg_cmd.conf file. The peer in the following line is md5:
Copy codeThe Code is as follows: local all md5 # replace peer with md5
After completing the preceding modification, reload postgresql:
Copy codeThe Code is as follows:
/Etc/init. d/postgresql reload