Install postgresql on an external DB server, Note that it fits a little when accessing the DB server with SSH connection from the client side.

·environment

--DB server OS: Red Hat 7.2.1-2 (Linux) --Client OS: Windows 10 --DB Connection Tool: A5M2

・ Construction

We will carry out in order.

Install postgreSQL on DB server

Install postgresql on Linux and make it a DB server

$ sudo yum install -y postgresql96 postgresql96-server postgresql96-libs postgresql96-contrib

DB initialization

I'm not sure, but there are various files, so Yoshi!

$ sudo /etc/init.d/postgresql96 initdb

Change password for postgre user

Enter a new password as in New password :, and then enter it again with Retype new password :.

$ sudo passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

Changes to postgresql.conf and pg_hba.conf

After initializing the DB with initdb, in the / var / lib / pgsql96 / data / directory Since postgresql.conf has been generated, edit it.

$ sudo vi /var/lib/pgsql96/data/postgresql.conf

When opened, move to near line 53 (It is convenient to remember that you can display the line number by entering: set number)

`postgresql.conf`


※Excerpt
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)

Uncomment listen_addresses and add the client's global IP address. I also uncomment the port.

~ After editing ~

`postgresql.conf`


※Excerpt
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = 'localhost,Global IP address'  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)

There are many ways to look up a global IP, It is recommended because you can see it instantly by accessing www.cman.jp with a client. 　https://www.cman.jp/network/support/go_access.cgi

Next, pg_hba.conf is generated in the same directory, so edit it.

$ sudo vi /var/lib/pgsql96/data/pg_hba.conf

This is at the bottom so you can skip to the end. Change as follows. (You can fly with Shift + g)

`pg_hba.conf`


# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host all all global IP address/32   trust

You have to set the client-side global IP address in the ADDRESS column and set the METHOD to trust. Please note that the client cannot connect to the DB server.

start postgreSQL

$ /etc/init.d/postgresql96 start

Database creation

If you don't create a database here I am having trouble connecting with the software for DB connection (A5M2 this time) on the client. Switch to postgres user first

$ sudo su - postgres
Last login: Sun Dec 9 14:02:32 UTC 2019 on pts/0
-bash-4.2$

Access DB with psql

-bash-4.2$ psql
psql (9.6.11)
Type "help" for help.

postgres=#

When postgres = # is displayed, literally create an empty database.

postgres=# create database hogeDB;

This completes the settings on the DB server side. Next is the client side settings (mainly A5M2)

・ Connection

Start A5M2 Add and remove databases Add → PostgreSQL (direct connection) For the settings on the "Basic" tab, enter the DB settings and enter them. Select the user name and private key required for the SSH connection in the SSH2 Tunnel tab.