A quick note mainly for myself on how to enable remote access in PostgreSQL and create its database dump.
An EC2-server moved to our AWS account with one website.
During an attempt to connect to the PostgreSQL running on this host – the “FATAL: Peer authentication failed for user “name”” appears.
And the same error if connect via localhost or by remote login.
PostgreSQL remote login enable
Usually, this error happens when PostgreSQL has no md5 Auth type configured for connections.
To enable the remote login – edit the /etc/postgresql/11/main/pg_hba.conf config and set values by:
type database-name username-name remote-address auth-type
In this current case it will look like:
...
host dbname username 188.***.***.94/32 md5
...
Where 188.***.***.94 is my home IP.
Applying PostgreSQL config changes with no reboot
The server is absolutely new, just came to us, I’m doing all of this on Friday evening and have no wish to reboot it now, but need to as I did changes in its /etc/postgresql/11/main/pg_hba.conf.
To apply those changes and make no reboot – switch to the postgres user:
root@ip-172-31-9-189:~# su - postgres
postgres@ip-172-31-9-189:~$
And run pg_ctl reload:
postgres@ip-172-31-9-189:~$ /usr/lib/postgresql/11/bin/pg_ctl reload -D /var/lib/postgresql/11/main
server signaled
Check process, that PostgreSQL is still running:
postgres@ip-172-31-9-189:~$ ps aux | grep post
postgres 10524 0.0 1.1 320924 24304 ? S Jul18 2:00 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
postgres 10526 0.0 1.4 321108 28588 ? Ss Jul18 0:02 postgres: 11/main: checkpointer
postgres 10527 0.0 0.5 320980 10936 ? Ss Jul18 0:36 postgres: 11/main: background writer
postgres 10528 0.0 0.4 320956 9496 ? Ss Jul18 0:43 postgres: 11/main: walwriter
postgres 10529 0.0 0.3 321416 7620 ? Ss Jul18 0:50 postgres: 11/main: autovacuum launcher
postgres 10530 0.0 0.2 176332 5820 ? Ss Jul18 1:10 postgres: 11/main: stats collector
postgres 10531 0.0 0.3 321264 6632 ? Ss Jul18 0:01 postgres: 11/main: logical replication launcher
And connect from the home IP:
[setevoy@setevoy-arch-pc ~] $ psql -h 18.***.***.27 -p 6666 -U username dbname
Password for user username:
...
dbname=>
All good so far.
Create a PostgreSQL database dump
And the last thing to create a database dump.
Use the pg_dump here:
[setevoy@setevoy-arch-pc ~] $ pg_dump -h 18.***.***.27 -p 6666 -U username dbname > dbname.pgsql
Password:
Check contents:
[setevoy@setevoy-arch-pc ~] $ head dbname.pgsql
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.3 (Ubuntu 11.3-1.pgdg18.04+1)
-- Dumped by pg_dump version 11.5
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
...
Done.
