PostgreSQL server image user guide

Before use

Q. What is PostgreSQL?

• PostgreSQL supports only the basic installation function that allows you to easily build PostgreSQL on your desired CentOS version with just a few settings and clicks. Except the provided installation function, technical support is not available.
• The service uses PostgreSQL, an open-source Object-Relational Database (ORDBMS) that emphasizes scalability and standards compliance.

Q. Is there anything I need to prepare in advance before creating a DB server?

• Currently, PostgreSQL DB is only accessible within NAVER Cloud Platform. Thus, the application server to access your DB server must also be in NAVER Cloud Platform.
• Before selecting a server type, consider what service will use the DB and how much the usage will be.

Q. What types of servers can be created as DB servers?

• In PostgreSQL, you can select one of many server types according to your need.

Q. What is the process for creating a DB server? You can easily create a server through the following steps after connecting to the console.

• Access console: connect to the console provided by NAVER Cloud Platform
• Set server: select storage type, server type, server name, and so on
• Set authentication key: use your own authentication key or create new if you don’t have any
• Set firewall: select your own ACG or create new
• Final confirmation: check server setting information such as server type and user-selected pricing plan, and create DB server

Q. What OS is installed when a DB server is created, and what size is the offered disk?

• The Linux OS will be installed.
• For PostgreSQL, a 50 GB basic disk is offered, which is included in the DB server fee.
• You can’t change the capacity of the default disk where the OS is installed.

Q. What is a database storage?

• You can select between an HDD and SSD the first time you create a DB. Different rates apply per storage type.
• For a service DB, use an SSD that supports high-performance I/O.

Q. What is the license for PostgreSQL?

• PostgreSQL is deployed with the PostgreSQL license, a flexible open-source license similar to the BSD or MIT licenses.
• For more information about PostgreSQL's GPL license, see The PostgreSQL Licence (PostgreSQL) | Open-Source Initiative.

Introducing PostgreSQL installation service

The following describes the concept and basic structure of NAVER Cloud's PostgreSQL installation service.

Introducing PostgreSQL

PostgreSQL is one of the world's most stable and progressive open-source DBMS. It supports flawless transactions (ACID) and high concurrency control (MVCC) for effective cost management.

NAVER Cloud PostgreSQL installation service configuration

Installation service

PostgreSQL installation service supported by NAVER Cloud supports the installation of the basic versions 9.4, 13.3, and 14.3, and you’ll be provided with a started-up PostgreSQL server. Except regarding DB installation, technical support is not available.

Installation method and package

You can install PostgreSQL through source compile / RPM / binary installation methods,
and NAVER Cloud provides an RPM installation image as a normal installation method.

RPM installation uses the .repo file officially provided by PostgreSQL (postgresql.org), and the latest repo version is included in the server image by default.

The following is the package list installed as the default settings.

• PostgreSQL 9.4

  • postgresql94-server
  • postgresql94
  • postgresql94-libs

• PostgreSQL 13.3

  • postgresql13-server
  • postgresql13
  • postgresql13-libs
  • postgresql13-contrib

• PostgreSQL 14.3

  • postgresql14-server
  • postgresql14
  • postgresql14-libs
  • postgresql14-contrib

Installation directory

Setting preferences for remote access (for both CentOS 6/7)

Because PostgreSQL allows access only from Localhost (127.0.0.1) when initially installed, remote access is not possible. Thus, to allow remote access from the PostgreSQL installation image in NAVER Cloud, the preferences file must be changed as follows:

• postgresql.conf

  This is a basic PostgreSQL DB parameter configuration file. Because PostgreSQL's Listen address default settings allow access only from Localhost (127.0.0.1), external access is not allowed. Thus, you must restart the server after changing this setting to IP * (all). The default port number is 5432 and can be changed as needed.

  vi /var/lib/pgsql/9.4/data/postgresql.conf
  * listen_addresses = '*'  # Allow remote access to all IPs.
  * port = 5432             # Set the desired port number.
  

• pg_hba.conf

  As an abbreviation for host-based authentication, this is an authentication configuration file. It consists of Host, Database, User, Address, and Method information.

  The following settings are applied by default, so you do not need to edit them separately.
  Change the account access method to the password method and send the password encrypted with md5.

  vi /var/lib/pgsql/9.4/data/pg_hba.conf
  * host    all             all             0.0.0.0/0                 md5
  

Manage PostgreSQL installation service

The following describes how to manage the PostgreSQL installation service.

Create PostgreSQL image server

If you create an image server with the PostgreSQL installation service, you’ll be provided with the started-up PostgreSQL DB installation and daemon.

Create server

The following describes how to create a server in which the PostgreSQL image is installed.

• Proceed by referring to the Server creation guide.

Step 1. Access management console

To access the management console to create or delete a server, click [Console] from NAVER Cloud Platform.

Step 2. Create server

① Go to the Services > Compute > Server menu from your console.

② Click [Create server]

Step 3. Select server image

① Select DBMS for the image type and PostgreSQL for the DBMS image type.

② Click [Next] on the right of the server image you want to use.

Step 4. Set server

① Set necessary information.

② Click [Next].

Step 5. Set authentication key

In this step, you can create a new authentication key or use the existing authentication key.

① If you’re a first-time user, select Create new authentication key and enter the authentication key name.

② Click Create and save authentication key and save the downloaded authentication key file (PEM file) to a safe location.

• This file will be used to create passwords for servers later.
• Although you use the same PEM file, different passwords are created for each server.

③ Click [Next].

Step 6. Set a firewall

In this step, you can create an Access Control Group (ACG) or select an ACG you have.

• See the ACG user guide.

① If you’re creating an ACG for the first time, click [Create ACG].

② Enter the ACG name.

③ In the ACG settings, enter a protocol, access source, and allowed port, and then create the ACG.

• Access source: enter the IP address or IP address range from which access is attempted.
  For example, enter just 10.64.51.205 for a single IP address, or enter 10.64.51.0/24 for the entire 10.64.51.XXX band. If an internal private IP address attempts external access through the NAT method, enter a public IP address.

• Allowed port: if you want to open ports individually, enter the required port number in Allowed port (service).
  If you need multiple ports, you can enter them by adding entries. To open all ports, enter 1-65535. (However, as your server becomes vulnerable to security threats in this case, enter a single port [e.g., 5432] if possible. Also, if specifying a single port, you must additionally register the ssh default port [22] or port forwarding port.)

④ Click [Next] on the right.

⑤ Check the settings.

⑥ Click [Create].

Step 7. Final confirmation

By this point, the PEM key file should be stored in a safe location on your computer, and you must be aware of the ACG settings. Note that if you don’t have a PEM key, you cannot access the server you’ve created.

① Click [Create Server].

② Wait until the server is created.

Initial server setting procedures

After creating the server, set a public IP address or port forwarding to access the server. To use GUI (pgAdmin) Tool, set a public IP address.

Set public IP address

To allow external access to the server you’ve created, you must set a public IP address.

• Proceed by referring to the Public IP user guide.

① Go to the Server > Public IP menu.

② Click [Request public IP].

• You can request public IP addresses up to the number of servers you have.

③ Select the server you’ve created.

④ Click [Next].

⑤ Click [Create].

⑥ The IP address is assigned in a few minutes.

• If a normal public IP address is assigned, you can directly access the server at 49.50.172.79:22 using a terminal program.

Set port forwarding for terminal access

To access the server using a public IP address specified in port forwarding information, you must set port forwarding.

• Proceed referring to the Port forwarding guide.

① Go to the Server > Server menu.

② Select the server and click [Set port forwarding] on the top.

③ In the port forwarding settings of the following page, register an external port for ssh access.

④ Register an external port and click [Add].

⑤ Click [Apply].

• With this port forwarding setting, you can now access the postgreSQL server name with the 49.50.172.79:1234 settings information.

Check server password

① Select a desired server.

② Click [Get admin password] from the [Manage servers] menu.

③ Drag and drop the file or click to upload the PEM file saved in my computer.

④ Click [Check password].

⑤ The admin password provided at initial creation is displayed.

• After the first login using the admin password provided at initial creation, you can change the password from your server for future use.

Access using terminal

The following describes how to access NAVER Cloud Platform PostgreSQL from your server using a terminal.

Access using public IP address

① Enter server access information.

• PORT: default port number (22)
• Public IP: issued public IP address (e.g., 49.50.172.79)

② Click [Open].

③ Click [Yes] to access.

④ Enter the root account name and click Enter.

⑤ In Admin password, enter the password you’ve checked.

Access through port forwarding

① Enter server access information.

• PORT: port number set through port forwarding (e.g., 1234)
• Public IP: public IP for server access specified in port forwarding page (e.g., 49.50.172.79)

② Click [Open]

③ Click [Yes] to access.

④ Enter the root account name and click Enter.

⑤ In Admin password, enter the password you’ve checked.

After creating server image

1. If you don’t want to use the server immediately after creation, use PostgreSQL daemon shutdown to avoid security threats.

2. Since a password is not set for the PostgreSQL you’ve created, make sure to set a password.

How to Start/Stop PostgreSQL

Use the following command to start and stop your PostgreSQL DB (run from the root account).

-- CentOS 6
shell> service postgresql-9.4 start
shell> service postgresql-9.4 stop

-- CentOS 7
shell> systemctl start postgresql-9.4
shell> systemctl stop postgresql-9.4

-- CentOS 7
shell> systemctl start postgresql-13 
shell> systemctl stop postgresql-13

-- CentOS 7
shell> systemctl start postgresql-14 
shell> systemctl stop postgresql-14

How to check DB running status and listen port

Use the following command to check the DB running status (run from the root account). After running the command, you should see the word "Running" on your CentOS 6/7 installation.

-- CentOS 6
Shell> service postgresql-9.4 status
-- CentOS 7
Shell> systemctl status postgresql-9.4
-- PostgreSQL 13.3 
Shell> systemctl status postgresql-13 
-- PostgreSQL 14.3 
Shell> systemctl status postgresql-14

Use the following command to check the listen port. After running the command, port 5432 should be in the Listen status.

Shell> netstat -tunlp | grep post

How to change PostgreSQL password

According to the PostgreSQL password policy, an initial password is not specified. When accessing for the first time, you must change the password. Use the following command to change your password.

Shell> sudo su - postgres
Shell> psql
postgres=> alter user postgres with password 'Password';

How to change PostgreSQL DB port

Since the port number 5432 for PostgreSQL access is a well-known port number for users, it is vulnerable to security threats. Thus, try to use your own unique port number (e.g., 15432). Additionally, try to set ACG’s allowed port number (e.g., 15432) as the same port number.

Use the following command to change your port number.

1. Change conf settings

-- PostgreSQL 9.4 
vi /var/lib/pgsql/9.4/data/postgresql.conf port = 15432  #Change the port number

-- PostgreSQL 13.3 
vi /var/lib/pgsql/13/data/postgresql.conf port = 15432  #Change the port number

-- PostgreSQL 14.3 
vi /var/lib/pgsql/14/data/postgresql.conf port = 15432  #Change the port number

2. Restart PostgreSQL

   # Root account
   -- CentOS 6
   -- PostgreSQL 9.4 
   shell> service postgresql-9.4 restart
   
   -- CentOS 7
   -- PostgreSQL 9.4 
   shell> systemctl restart postgresql-9.4 
   -- PostgreSQL 13.3 
   shell> systemctl restart postgresql-13 
   -- PostgreSQL 14.3 
   shell> systemctl restart postgresql-14  
   

3. Check the changed listen port number

   shell> netstat -tunlp | grep post
   

How to access PostgreSQL DB locally

Shell> sudo su - postgres
Shell> psql
postgres=# \h  >> View Help
postgres=# \q  >> Exit

If you have changed the port number, you can access the DB as follows:

Shell> psql -p 15432
or
Shell> export PGPORT=15432
Shell> psql

How to access PostgreSQL from remote server (web server or other DBs)

Shell> sudo su - postgres
Shell> psql -h IP address -p 5432 -U user name -d DB name
       Password for user : Enter password
postgres=# \h  >> View Help
postgres=# \q  >> Exit

How to access PostgreSQL using GUI (pgAdmin) Tool

Since you need to access the PostgreSQL server in the Windows environment of your PC, you must additionally assign a public IP address to the PostgreSQL server. Select Request public IP in the Public IP menu, and click the relevant server in Select server to apply.

For more information about pgAdmin Tool, visit the address below.

• pgAdmin download: https://www.pgadmin.org/download
• pgAdmin manual: https://www.pgadmin.org/docs

How to change default data directory

1. postgres daemon stop

   -- cent 6
   service postgresql-9.4 stop
   
   -- cent 7
   -- PostgreSQL 9.4 
   systemctl stop postgresql-9.4 
   
   -- PostgreSQL 13.3 
   shell> systemctl stop postgresql-13 
   
   -- PostgreSQL 14.3 
   shell> systemctl stop postgresql-14
   

2. Create new directory

   mkdir -p /var/lib/pgsql/NEW_PGDATA
   

3. Change directory owner and permissions (700 required)

   chown -R postgres:postgres /var/lib/pgsql/NEW_PGDATA
   chmod 700 -R /var/lib/pgsql/NEW_PGDATA
   

4. Change the .bash_profile environment variable for postgres user

   vi /var/lib/pgsql/.bash_profile
   Before change : PGDATA=/var/lib/pgsql/9.4/data
   After change : PGDATA=/var/lib/pgsql/NEW_PGDATA
   

5. Change pstgres service settings

• Replace ${version} with either 9.4, 13, or 14 according to the installed version

  -- cent 6
  # Execute as root user
  vi /etc/init.d/postgresql-${version}
  >> Change the PGDATA and PGLOG directory paths.
  Before change : PGDATA=/var/lib/pgsql/{version}/data
  After change : PGDATA=/var/lib/pgsql/NEW_PGDATA/data
  
  Before change : PGLOG=/var/lib/pgsql/{version}/pgstartup.log
  Before change : PGLOG=/var/lib/pgsql/NEW_PGDATA/pgstartup.log
  
  -- cent 7
  # Execute as root user
  vi /usr/lib/systemd/system/postgresql-${version}.service
  Before change : Environment=PGDATA=/var/lib/pgsql/${version}/data/
  After change : Environment=PGDATA=/var/lib/pgsql/NEW_PGDATA/data
  

6. Migrate data to new Postgres datadir

• Replace ${version} with either 9.4, 13, or 14 according to the installed version

  # Execute as postgres user
  mv /var/lib/pgsql/${version}/data /var/lib/pgsql/NEW_PGDATA/
  

7. Delete the postmaster.opts file

   rm /var/lib/pgsql/NEW_PGDATA/data/postmaster.opts
   

8. postgres daemon reload & start

• Replace ${version} with either 9.4, 13, or 14 according to the installed version

  -- cent 6
  service postgresql-${version} reload
  service postgresql-${version} start
  -- cent 7
  systemctl daemon-reload
  systemctl start postgresql-${version}
  

Terminating PostgreSQL image server

When terminating your server where PostgreSQL was created in NAVER Cloud, terminate it after the following procedures.

1. Back up data saved in PostgreSQL, download it, and store it separately

   Shell> sudo su - postgres
   Shell> pg_dumpall > 'Name of the dump file to be saved'
   

2. Stop PostgreSQL server

3. Delete data directory where PostgreSQL is stored

   • E.g., rm -rf /var/lib/pgsql

4. Stop server from the NAVER Cloud console first -> Terminate.

Go to related information

You can see related information in the following official manuals.

• Manual (English):
  https://www.postgresql.org/docs/en/9.4/static/index.html
  https://www.postgresql.org/docs/en/13/static/index.html
  https://www.postgresql.org/docs/en/14/static/index.html