AWS EC2上部署PostgreSQL + TimeScaleDB

在EC2上部署PostgreSQL+TimescaleDB

首先，创建一个Ubuntu实例，使用ssh连接到这个服务器上（使用WSL进行连接可以看这一篇博文AWS EC2上创建一个Ubuntu Instance 并使用WSL连接)）。连接好之后，就可以按照Timescale文档，进行PostgreSQL+TimeScaleDB的安装和部署了。

# `lsb_release -c -s` should return the correct codename of your OS
echo "deb <http://apt.postgresql.org/pub/repos/apt/> $(lsb_release -c -s)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

# Add our PPA
sudo add-apt-repository ppa:timescale/timescaledb-ppa
sudo apt-get update

# Now install appropriate package for PG version
sudo apt install timescaledb-2-postgresql-13

sudo timescaledb-tune (y/y/n)

# Restart PostgreSQL instance
sudo service postgresql restart

按照以上步骤操作完成之后，PostgreSQL就已经安装成功了。可以通过psql --version查看PostgreSQL版本信息。接下来，为了让我们的PostgreSQL被远程访问，我们需要对它进行配置。

sudo nano /etc/postgresql/13/main/postgresql.conf

打开配置文件，找到

#listen_addresses = 'localhost'

去掉注释，修改为（这样修改允许数据库能够监听所有的地址）

listen_addresses = '*'

接下来打开另外一个配置文件进行修改

sudo nano /etc/postgresql/13/main/pg_hba.conf

找到最后几行，修改为如下样式，这样修改是为了能够远程访问到数据库。

pg_hba-conf

然后重新启动PostgreSQL服务sudo service postgresql restart，配置完成之后，输入命令sudo -u postgres psql postgres使用postgreSQL默认的用户postgres进入数据库，使用\password设置密码，然后就可以正常使用数据库了。

使用pgAdmin连接

打开pgAdmin，右键Servers→Create→Server...，和正常连接到数据库一样，首先给一个命名，然后在Connection中，和在本地连接数据库一样，填写相关的信息，密码就是云端上数据库的密码。

pgAdmin-connection

然后，在SSH Tunnel中，在Tunnel host输入AWS EC2实例的public IPv4 DNS，Tunnel port填22，username就是服务器的用户名，Ubuntu服务器的默认用户名就是ubuntu。Authentication选择Identity file，然后Identity file一栏选中之前下载的密钥对

pgAdmin-SSH

点击Save按钮就可以成功连接。

命令行远程连接数据库

默认情况下，AWS EC2开放的端口只有SSH默认的端口22，但是我们想要远程访问的是PostgreSQL的端口5432，所以在默认情况下，我们无法远程访问PostgreSQL数据库。在我们输入命令

psql postgres://username:password@hostname:port/dbname

之后，就会得到报错信息

psql: 错误: 无法联接到服务器: Connection timed out (0x0000274C/10060)
        服务器是否在主机 "hostname" 上运行并且准备接受在端口
5432 上的 TCP/IP 联接?

这个错误信息就提示我们需要打开AWS EC2数据库中的5432端口，我们才可以对数据库进行远程访问。首先，我们需要打开EC2控制台，然后打开Security Groups (安全组)，选中一个安全组，然后进入Actions下的Edit inbound rules (编辑入站规则)。

edit-bound-rules

默认情况下只有一个类型为SSH的规则，点击Add rule，类型选择PostgreSQL，点击Save rules保存即可。

inbound-rules

然后我们直接在命令行中使用命令就可以进行连接啦

psql postgres://postgres:yourpassword@ec2-34-200-222-92.compute-1.amazonaws.com:5432/postgres

同样的，我们可以使用Java中的jdbc连接数据库

public static void main(String... args) {
      final String connUrl = "jdbc:postgresql://ec2-34-200-222-92.compute-1.amazonaws.com:5432/postgres?user=postgres&password=yourpassword";
      try (Connection conn = DriverManager.getConnection(connUrl)) {
          System.out.println(conn.getClientInfo());
      } catch (SQLException ex) {
          System.err.println("SQLState: " + ex.getSQLState() +  "\\t" + ex.getMessage());
      }
  }

显示结果

{ApplicationName=PostgreSQL JDBC Driver}

成功连接！