The PostgreSQL database installation configuration instance in Debian

Used the MySQL, want to try to use the PostgreSQL, it is said that this thing is also very strong, I search on the Internet under the relevant Chinese books, but books is far from MySQL so much, but I find the translated by volunteers in official documents (refer to 9.1 document translation projects), to be honest I still more tangled English document reading ability, so have a document in Chinese, of course, give priority to Chinese.

Under Debian you can install

directly from the apt-get command

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

After the installation is complete, PostgreSQL default created called postgres users, this and MySQL root and SQL Server sa account, is the super administrator account, unlike MySQL, it also creates postgres Unix system account, and SQL Server master database, PostgreSQL default database is template1, psql you can use the command line management tool to manage it, The prerequisite, of course, is to switch to postgres's system account:

# su - postgres
$ psql

Or you can use sudo to run psql as postgres all at once, saving switching to

sudo su postgres -c psql template1

After completing the above command, you should be able to see the command interface for database operation. To connect to the specified database, use a command like \c databasename.

Ok, let's type \q to exit the command interface first, and then we need to create a normal account, as it is recommended that we do not directly use the super administrator postgres account to operate our own database.

Start by creating a system account named mypguser.

sudo adduser mypguser

Next, use postgres to connect template1 and enter the psql command interface:

sudo su postgres -c psql template1

Create a new database user, a new database, and give the new user the full privileges of the new database:

postgres=# 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 shell command createuser mypguser and createdb mypgdatabase respectively:

# createuser mypguser    #from regular shell
# su - mypguser
$ psql postgres
postgres=# CREATE DATABASE mypgdatabase;

When this is done, let's exit the psql console using the \q command.

postgres=# \q

Now we can manage our database with the specified account:

sudo su mypguser -c 'psql -d mypgdatabase'

Wait, the most important step for us is to reset our postgres account password by following the command:

sudo su postgres -c psql template1 
template1=# ALTER USER postgres WITH PASSWORD 'yourpassword' 
template1=# \q

Don't forget the password for your system account:

sudo passwd -d postgres

Okay, that's the end of the simple installation and account creation. Here's what could go wrong:

If the following error occurs:

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

. Please edit your pg_hba conf, this file is usually located in/etc postgresql/X Y/main/pg_hba conf, X. Is your PostgreSQL Y version number, the following lines peer trust instead:

local   all    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_hba.conf file so that peer is md5:

local   all    all    md5       # replace peer with md5

Please reload postgresql:
after the above modification

/etc/init.d/postgresql reload