PostgreSQL Database installation Configuration instance in Debian _postgresql

Accustomed to MySQL, want to try to use the next PostgreSQL, it is said that this thing is also very strong, I searched the Internet related Chinese books, but the related books are far less than MySQL, but I found in the official translation by the Volunteers documents (refer to 9.1 document translation project), To tell the truth, my English document reading ability is still more tangled, so there are Chinese documents of course priority in Chinese.

It can be installed directly under Debian via the Apt-get command:

Copy Code code as follows:

sudo apt-get install PostgreSQL postgresql-client postgresql-server-dev-all

After the installation is complete, PostgreSQL creates a user named Postgres by default, which is the same as the SA account for MySQL and SQL Server, and is the Super Admin account, unlike MySQL, which also created the Postgres Unix system account , like the master database for SQL Server, the default database for PostgreSQL is template1, and you can manage it using the command-line administration tool Psql, if you want to switch to Postgres's system account:

Copy Code code as follows:

# Su-postgres
$ psql

Or you can use sudo to one-step postgres identity to run Psql, save switching to switch to:

Copy Code code as follows:

sudo su postgres-c psql template1

After completing the above command you should be able to see the command interface of the operation database, note that \q is the Exit command, each SQL statement ends with an English semicolon. If you want to connect to the specified database, use a command such as \c DatabaseName.

OK, let's type \q to exit the command interface, and then we need to create a regular account because we don't recommend using the Super Admin Postgres account directly to manipulate our own database.

First, create a system account named Mypguser.

Copy Code code as follows:

sudo adduser mypguser

Second, use Postgres to connect template1 and enter the Psql command interface:

Copy Code code as follows:

sudo su postgres-c psql template1

Create a new database user, a new database, and give the new user full permissions for the new database:

Copy Code code as follows:

postgres=# CREATE USER mypguser with PASSWORD ' Mypguserpass ';
postgres=# CREATE DATABASE mypgdatabase;
postgres=# GRANT all privileges on the DATABASE mypgdatabase to Mypguser;

Of course, the above actions can be implemented in shell command CreateUser mypguser and Createdb mypgdatabase respectively:

Copy Code code as follows:

# CreateUser Mypguser #from Regular shell
# Su-mypguser
$ psql Postgres
postgres=# CREATE DATABASE mypgdatabase;

Once this is done, let's exit the Psql console using the \q command.

Copy Code code as follows:

postgres=# \q

Below we can use the designated account to manage our database:

Copy Code code as follows:

sudo su mypguser-c ' psql-d mypgdatabase '

Wait, we have the most important step is to reset our Postgres account password, through the following command:

Copy Code code as follows:

sudo su postgres-c psql template1
template1=# ALTER USER postgres with PASSWORD ' YourPassword '
template1=# \q

Don't forget the password for the SYSTEM account:

Copy Code code as follows:

sudo passwd-d postgres

All right, here we go. Simple installation and creation account has been explained, to introduce possible errors:

If the following error occurs:

Copy Code code as follows:

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

Please edit your pg_hba.conf, this file is generally located in/etc/postgresql/x.y/main/pg_hba.conf,x.y is your PostgreSQL version number, the following line of peer to trust:

Copy Code code as follows:

Local all trust # Replace ident or peer with trust

If the following error occurs:

Copy Code code as follows:

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

Please still modify the pg_hba.conf file, the following line of peer is MD5:

Copy Code code as follows:

MD5 # Replace peer with MD5

Please reload PostgreSQL after completing the above modifications:

Copy Code code as follows:

/etc/init.d/postgresql Reload