How to install PostgreSQL on Ubuntu 16.04

Introduction

PostgreSQL is an object [relational database] (https://cloud.tencent.com/product/cdb-overview?from=10680) management system (ORDBMS) based on version 4.2 developed by the Department of Computer Science, University of California, Berkeley. PostgreSQL supports most of the SQL standards and provides many other modern features: complex queries, foreign keys, triggers, views, transaction integrity, MVCC. Similarly, PostgreSQL can be extended in many ways, for example, by adding new data types, functions, operators, aggregate functions, and indexes. Use, modify, and distribute PostgreSQL for free, whether for private, commercial, or academic research. The [PostgreSQL] (http://www.postgresql.org/) relational database system is a powerful, scalable, and standard open source database platform. This tutorial will help you install and configure PostgreSQL on the Tencent Cloud CVM server on Ubuntu 16.04 LTS (Xenial Xerus).

Preparation

• Set Tencent Cloud CVM server host name and time zone. Students who don’t have a server can buy it from here, but I personally recommend you to use the free Tencent Cloud Developer Lab for experimentation, and then buy server.
• Create standard user accounts, strengthen SSH access and delete unnecessary network services.
• Update your system:

sudo apt-get update && sudo apt-get upgrade

note
This tutorial is written for non-root users. Commands that require elevated privileges are prefixed with sudo.

Install PostgreSQL

Install PostgreSQL from the Ubuntu package repository:

sudo apt-get install postgresql postgresql-contrib

Configure PostgreSQL

Modify Postgres user

By default, PostgreSQL will create a Linux user named postgres to access the database software.

caveat
Users using postgres should not be used for other purposes (for example, to connect to other networks). Doing so will pose a serious threat to the security of the database.

• Change the Linux password of the postgres user:

sudo passwd postgres

• Issue the following command to set a password for the postgres database user. Be sure to replace newpassword with a strong password and keep it in a safe place.

su - postgres
psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

Please note that this user is different from the postgresLinux user. Linux users are used to access the database, and PostgreSQL users are used to perform management tasks on the database.

The password set in this step will be used to connect to the database via the network. By default, peer authentication will be used for local connections.

Create database

Run the commands in this section as the postgresLinux user.

• Create a sample database mytestdb:

createdb mytestdb

• Connect to the test database:

psql mytestdb

• You will see the following output:

psql(9.5.2)
Type "help"for help.
​
mytestdb=#

This is the PostgreSQL client shell where you can issue SQL commands. To view the list of available commands, use the \h command. You can learn more about adding specific commands after \h.

Create table

This section contains examples of creating a test database using the first and last names of employees, assigning a unique key to each name. When creating your own table, you can specify as many parameters (columns) as needed and name them appropriately. Run the commands in this section from the PostgreSQL shell opened in step 2 of the Create Database section.

• Create a table named "employees" in the test database:

CREATE TABLE employees(employee_id int, first_name varchar, last_name varchar);

• Insert a record in the table:

INSERT INTO employees VALUES(1,'John','Doe');

• View the contents of the "employees" table:

SELECT * FROM employees;

This will produce the following output:

 employee_id | first_name | last_name
- - - - - - - - - - - - - +- - - - - - - - - - - - +- - - - - - - - - - - 1| John       |Doe(1 row)

Enter the \q command to exit the PostgreSQL shell.

Create PostgreSQL role

PostgreSQL grants database access permissions through roles used to specify permissions. Roles can be understood as having functions similar to Linux "users". In addition, roles can also be created as a set of other roles, similar to Linux "groups." PostgreSQL roles are globally applicable, so you don’t need to create them. If you want to grant access to multiple databases on the same server, use the same twice Roles.

The example commands in this section should be run as the postgresLinux user.

• Add a new user role at the prompt, and then add the password:

createuser examplerole --pwprompt

If you need to delete a role, you can use the dropuser command instead of createuser.

• Connect to the database:

psql mytestdb

By default, you will connect as the postgres database user.

• In the PostgreSQL shell, enter the following to grant all permissions on the employee table to the user examplerole:

GRANT ALL ON employees TO examplerole;

Type \q to exit the PostgreSQL shell.

Ensure the security of local PostgreSQL access

PostgreSQL uses peer authentication by default. This means that the database connection will be granted to local system users who own or have permissions to the connected database. This type of authentication is very useful in situations where a specific system user will run local programs (for example, scripts, CGI/FastCGI processes owned by different users, etc.), but for higher security, you may want to require a password to access Your database.

Unless otherwise noted, the commands in this section should be run as the postgresLinux user.

• Edit the /etc/postgresql/9.5/main/pg_hba.conf file under the header of # "local" is for Unix domain socket connections only:

# " local" is for Unix domain socket connections only
local    all        all             peer`

Replace peer with md5 on this line to activate password authentication with MD5 hash.

• To enable these changes, we need to restart PostgreSQL. However, for security reasons, we did not grant sudo permissions to the postgres user. Return to the normal user shell:

exit

• Restart PostgreSQL and switch back to the postgres user:

sudo service postgresql restart
su - postgres

• Use postgres and connect to the test database as the examplerolePostgreSQL user:

psql -U examplerole -W mytestdb

You will be prompted to enter the password of the examplerole user and grant shell access to the database by psql. When using a database, you can use the \z command to check the access permissions of each table.

More information

For additional information on this topic, you may want to refer to the following resources. Although we hope to provide these useful, please note that we cannot guarantee the accuracy or timeliness of externally hosted materials.

PostgreSQL online documentation

psql man page

Self-built servers will inevitably encounter such problems. Configuring SSL is very troublesome. Although it is a kind of fun for some people, if you use it in a production environment, I still recommend that you directly use cloud relational database ), the cloud relational database allows you to easily deploy, manage and expand relational databases in the cloud, providing safe, reliable, scalable and flexible on-demand cloud database services. Tencent Cloud relational database provides MySQL, SQL Server, MariaDB, PostgreSQL database engine, and optimizes the performance of the database engine. The cloud relational database is a highly available hosting service that provides a complete set of database operation and maintenance solutions such as disaster recovery, backup, recovery, monitoring, migration, etc. It can free you from time-consuming database management tasks and let you have more Focus on your application and business more time.

If you don't want to configure MySQL, you can directly use the cloud database. TencentDB for MySQL allows users to easily deploy and use MySQL database in the cloud.

For more Linux and PostgreSQL tutorials, please go to [Tencent Cloud + Community] (https://cloud.tencent.com/developer?from=10680) to learn more.

Reference: "How to Install PostgreSQL on Ubuntu 16.04"