Ubuntu PostgreSQL Installation and configuration

1. Installation

The latest version is automatically installed using the following command, 9.5

sudo apt-get install PostgreSQL

When the installation is complete, the default is:

(1) Create a Linux user named "Postgres"

(2) Create a default database account named "Postgres" with no password as the database administrator

(3) Create a table named "Postgres"

Some of the default information after the installation is complete is as follows:

Config/etc/postgresql/9.5/main
Data/var/lib/postgresql/9.5/main
Locale en_US. UTF-8
Socket/var/run/postgresql
Port 5432

2. Psql command

After installation there will be PostgreSQL client psql, through sudo-u postgres psql Enter, the prompt becomes: postgres=#

The basic commands for executing SQL statements and Psql are available here. The following basic commands are available:

\ password: set password
\ q: exit
\ h: View the explanation of SQL commands, such as \ h select.
\ ?: View the list of psql commands.
\ l: List all databases.
\ c [database_name]: Connect to another database.
\ d: List all tables in the current database.
\ d [table_name]: List the structure of a table.
\ du: List all users.
\ e: Open a text editor.
\ conninfo: List information about the current database and connection.

Second, modify the database default account password 1, login

The command to log in to the database using the Psql command is:

Psql-u dbuser-d exampledb-h 127.0.0.1-p 5432

The parameters of the above command have the following meanings:-u Specify the user,-D specify the database,-h specifies the server,-p specifies the port.

After entering the above command, you will be prompted to enter the password for the Dbuser user.

The psql command has a shorthand form :

If the current Linux system user is also a PostgreSQL user, you can omit the user name (the part of the-u parameter)

If a database with the same name as the current system user exists inside PostgreSQL, the database name can also be omitted.

2, modify the default Administrator account password

The Linux user "Postgres" identity (at this time only the user has the Psql command) to execute the Psql client, enter the client's prompt interface (here the system user name, database username, database name is Postgres, so you can use shorthand form)

Sudo-u Postgres Psql

postgres=# alter user postgres with password ' 123456 ';

This way, the administrator "Postgres" The password is "123456".

Exit psql Client Command: \q

To remove the administrator's password, the command is available: sudo-u postgres psql-d Postgres

Third, modify the password of the Linux user

This actually has little to do with the installation of PostgreSQL.

Take the Linux user "Postgres" as an example, run the passwd command on it:

[Email protected]:/etc/postgresql/9.5/main$ sudo-u postgres passwd//can also sudo passwd postgres

Changing password for postgres.
(current) UNIX password: 
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully

Iv. Configuring the database to allow remote connection access

After the installation is complete, the database can only be connected locally, and the other machines will not be able to access it.

1. Modify the Listening address

Remove the comment #listen_addresses = ' localhost ' and change it to listen_addresses = ' * '

2. Modify the IP segment of the accessible user

Add at the end of the file: host all 0.0.0.0 0.0.0.0 MD5, which means running any IP connection

3. Restart the database

Sudo/etc/init.d/postgresql restart

Other: Managing users, establishing databases, etc.

V. Add new user and new database law one: using PostgreSQL client Psql

Run the Psql command of the system user "Postgres" to enter the client:

Sudo-u Postgres Psql

Create the user "Xiaozhang" and set the password:

postgres=# Create user Xiaozhang with password ' 123456 ';

Create Database Exampledb, owner is Xiaozhang:

postgres=# CREATE DATABASE exampledb owner Xiaozhang;

Assign all the permissions of the Exampledb database to Xiaozhang, otherwise Xiaozhang can only log on psql, without any database operation permissions:

Grant all privileges on the database exampledb to Xiaozhang;

Method Two: Using the shell command line

The CreateUser and createdb command-line programs are available after installing PostgreSQL.

First create the database user "Xiaozhang1" and specify it as Superuser:

Sudo-u postgres CreateUser--superuser xiaozhang1;

Then log in to Psql console to set its password and exit:

 
[email protected]:~$ sudo -u postgres psql psql (9.5.3)
Type "help" for help. postgres=# \password xiaozhang1; Enter new password: 
Enter it again: postgres=# \q

Then create the database under the shell command line and specify the owner:

Sudo-u postgres createdb-o xiaozhang1 exampledb1;

Method Three: Use Paadmin3 to create after the administrator connects to the database

After the act of Law One, Act two operation, executes postgres=# \DU obtains the user list as follows:

Execute postgres=# \l Get the list of databases as follows:

To remove a user (such as deleting a Xiaozhang), postgres=# The drop database example, and then postgres=# the drop to user Xiaozhang;.

VI. Basic Database Operations commands

# Create new table
CREATE TABLE user_tbl (name VARCHAR (20), signup_date DATE);
# Insert data
INSERT INTO user_tbl (name, signup_date) VALUES (‘Zhang San’, ‘2013-12-22’);
# Select record
SELECT * FROM user_tbl;
# update data 
UPDATE user_tbl set name = ‘Li Si’ WHERE name = ‘Zhang San’;
# Delete Record 
DELETE FROM user_tbl WHERE name = ‘李四’;
# Add field
ALTER TABLE user_tbl ADD email VARCHAR (40);
# Update structure
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# Rename field
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# Delete field
ALTER TABLE user_tbl DROP COLUMN email;
# Form rename
ALTER TABLE user_tbl RENAME TO backup_tbl;
# Delete form
DROP TABLE IF EXISTS backup_tbl; 

Ubuntu PostgreSQL Installation and configuration