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.
--DB server OS: Red Hat 7.2.1-2 (Linux) --Client OS: Windows 10 --DB Connection Tool: A5M2
We will carry out in order.
Install postgresql on Linux and make it a DB server
$ sudo yum install -y postgresql96 postgresql96-server postgresql96-libs postgresql96-contrib
I'm not sure, but there are various files, so Yoshi!
$ sudo /etc/init.d/postgresql96 initdb
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.
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.
$ /etc/init.d/postgresql96 start
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)
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.
If you make a test connection here and get the message "The connection was successful", you are successful!
After that, save the settings and boil it.
reference: Access the DB of the external server (postgresql) https://liginc.co.jp/programmer/archives/1598
Recommended Posts