如何在Ubuntu云服务器上安装使用PostgreSQL？-朝暮数据

PostgreSQL是一款开源的关系型数据库系统，支持大部分SQL标准，包括外键和事务。这款数据库由社区开发，免费、安全、扩展性强。很多知名公司的网站都在使用PostgreSQL，如Apple、Reddit、Spotify、NASA等。

这篇帮助以一台Ubuntu 20.04云服务器为例，介绍安装和设置PostgreSQL的方法。我们会创建一个数据库，执行一些基本的数据库操作，然后学习如何创建数据库备份并且还原备份。

安装PostgreSQL

更新Ubuntu到最新状态。

$ sudo apt -y update

安装PostgreSQL核心数据库以及依赖软件。

$ sudo apt install -y postgresql postgresql-contrib

验证PostgreSQL安装状态。

$ dpkg --status postgresql
Package: postgresql
Status: install ok installed
...
Version: 12+214ubuntu0.1

以下是PostgreSQL的一些基本信息。

默认端口：5432
配置文件：/etc/postgresql/12/main/postgresql.conf
数据库目录：/var/lib/postgresql/12/main
服务名称：postgresql

以下是PostgreSQL的一些基本操作。

停止PostgreSQL。

$ sudo systemctl stop postgresql

启动PostgreSQL。

$ sudo systemctl start postgresql

重启PostgreSQL。

$ sudo systemctl restart postgresql

重载PostgreSQL。

$ sudo systemctl reload postgresql

检查PostgreSQL状态。

$ sudo systemctl status postgresql

设置PostgreSQL

PostgreSQL默认带有一个命令行工具psql，用于登录数据库。同时创建了一个用户postgres，作为PostgreSQL的超级用户。

以postgres用户身份执行psql工具。

$ sudo -u postgres psql

执行上面的命令后，系统会显示PostgreSQL提示符，说明PostgreSQL随时可以接受SQL命令了。

postgres=#

首次安装PostgreSQL后，需要设置postgres用户的密码。执行以下命令，并输入两次密码。

postgres=# \password postgres
Enter new password:
Enter it again:

退出命令行工具。

postgres=# \q

postgres用户密码设置成功后，我们可以创建测试数据库，并执行一些基本的数据库操作。

创建PostgreSQL数据库

使用CREATE DATABASE创建测试数据库。

postgres=# CREATE DATABASE test_db;

列出已创建的数据库。

postgres=# \l

切换数据库，以test_db数据库为例。

postgres=#  \c test_db;
test_db=#

接下来可以在数据库中创建数据表了。

创建PostgreSQL数据表

下面创建一个简单的customers用户表。

test_db-# CREATE TABLE customers (
          customer_id SERIAL PRIMARY KEY,
          first_name VARCHAR (50),
          last_name VARCHAR (50),
          phone VARCHAR (10)
          );

列出已创建的数据表。

test_db-# \dt;

查看某个数据表信息。

test_db=# \d customers;

   Column    |         Type          | Collation | Nullable |                    Default
-------------+-----------------------+-----------+----------+------------------------------------------------
 customer_id | integer               |           | not null | nextval('customers_customer_id_seq'::regclass)
 first_name  | character varying(50) |           |          |
 last_name   | character varying(50) |           |          |
 phone       | character varying(10) |           |          |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customer_id)
Press Q to get back to the test_db=# prompt.

向数据表插入数据。

test_db=# INSERT INTO customers(first_name, last_name, phone) VALUES ('JOHN', 'DOE', '11111');
test_db=# INSERT INTO customers(first_name, last_name, phone) VALUES ('MARY', 'ROE', '33333');
test_db=# INSERT INTO customers(first_name, last_name, phone) VALUES ('JANE', 'SMITH', '55555');

查看数据表中的数据。

test_db=# SELECT * FROM customers;

 customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
           1 | JOHN       | DOE       | 11111
           2 | MARY       | ROE       | 33333
           3 | JANE       | SMITH     | 55555
(3 rows)

更新数据表中的数据。

test_db=# UPDATE customers SET phone = '88888' WHERE customer_id = 1;

删除数据表中的数据。

test_db=# DELETE FROM customers WHERE customer_id = 2;

备份PostgreSQL数据库

PostgreSQL自带备份和还原数据库的工具。以test_db数据库为例，创建压缩格式的备份命令如下，需要输入postgres用户的密码。

$ pg_dump -d test_db -U postgres | gzip > test_db_backup.sql.gz

创建SQL格式的备份命令如下，同样需要输入postgres用户的密码。

$ pg_dump -U postgres -f test_db_backup.sql test_db

还原PostgreSQL数据库

以postgres用户身份执行psql工具。

$ sudo -u postgres psql

切换到test_db数据库，删除customers数据表。然后还原数据库，确认该数据表是否恢复。

postgres=# \c test_db;
postgres=# DROP TABLE customers;
postgres=# \q

从压缩格式的备份中还原。

$ gunzip -c test_db_backup.sql.gz | psql -U postgres -d test_db

从SQL格式的备份中还原。

$ psql -U postgres -d test_db -f test_db_backup.sql

再次登录数据库，确认customers数据表是否恢复。

$ sudo -u postgres psql

切换到test_db数据库，查看数据表中的数据。

postgres=# \c test_db;
test_db=# SELECT * FROM customers;

 customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
           3 | JANE       | SMITH     | 55555
           1 | JOHN       | DOE       | 88888
(2 rows)

到此为止，我们学习了如何在Ubuntu 20.04云服务器上安装和设置PostgreSQL，然后使用基本的SQL语法创建数据库，并且执行一些基本的CRUD操作。最后使用命令行备份和还原数据库。了解PostgreSQL的基本使用方法后，我们可以使用PHP、Node.js或者Python等语言，结合PostgreSQL创建应用程序或者网站。