Ubuntu under PostgreSQL installation configuration

One, install and configure, and set the remote login user name and password

1. Installing PostgreSQL

sudo apt-get update

sudo apt-get install postgresql-9.4

• After installing PostgreSQL under Ubuntu, it is automatically registered as a service and starts automatically with the operating system.
• After installing PostgreSQL under Ubuntu, an operating system user named Postgres is automatically added and the password is random. and will automatically generate a database named Postgres, the user name is Postgres, the password is also random.

2, modify the Postgres database user's password is 123456

Open Client Tools (PSQL)

Sudo-u Postgres Psql

• Among them, Sudo-u Postgres is the use of Postgres user login meaning
• PostgreSQL data By default creates a Postgres database user as the administrator of the database and the password is random

postgres=# ALTER USER postgres with PASSWORD ' 123456 ';

• Postgres= #为PostgreSQL下的命令提示符,--note the last semicolon;

3. Exit the PostgreSQL psql client

postgres=# \q

4, modify the Ubuntu operating system Postgres user's password (password to be the same as the database user postgres password)

Switch to root user

Su Root

Remove PostgreSQL user Password

sudo passwd-d postgres

• Passwd-d is the meaning of emptying the specified user password

Set the password for the PostgreSQL system user

Sudo-u Postgres passwd

Follow the prompts and enter your new password two times

• Enter a new UNIX password
• Re-enter the new UNIX password
• passwd: Password has been successfully updated

5. Modify the Postgressql database configuration for remote access

Vi/etc/postgresql/9.4/main/postgresql.conf

1. Listen for any address access, modify connection permissions

#listen_addresses = ' localhost ' changed to listen_addresses = ' * '

2. Enable password verification

#password_encryption = on change to password_encryption = On

Vi/etc/postgresql/9.4/main/pg_hba.conf

Add the following to the end of the document

Host all 0.0.0.0 0.0.0.0 MD5

6. Restart Service

/etc/init.d/postgresql restart

7, 5432 Port firewall settings

5432 is the default port for PostgreSQL

Iptables-a input-p tcp-m State--state new-m TCP--dport 5432-j ACCEPT

Ii. internal Login, management database, new database, user and password

1. Log in to Postgre SQL database

Psql-u postgres-h 127.0.0.1

2, create a new user Zhangps, but do not give permission to build the database

postgres=# Create user "Zhangps" with password ' 123456 ' nocreatedb;

• The user name is double quotation marks

3. Establish the database and specify the owner

postgres=#CREATE DATABASE "TestDB" with owner = "Zhangps";

Third, external login, management database, new database, user and password

1, in the external command line Management command, create user pencil

Sudo-u postgres createuser-d-P pencil

• Enter a new password:
• Enter the new password again:

2. Build the database (tempdb) and specify the owner as (pencil)

Sudo-u postgres createdb-o Pencil tempdb

• -O set owner to pencil

Ubuntu under PostgreSQL installation configuration