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.
Contents
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:
[simterm]
root@ip-172-31-9-189:~# su - postgres postgres@ip-172-31-9-189:~$
[/simterm]
And run pg_ctl reload:
[simterm]
postgres@ip-172-31-9-189:~$ /usr/lib/postgresql/11/bin/pg_ctl reload -D /var/lib/postgresql/11/main server signaled
[/simterm]
Check process, that PostgreSQL is still running:
[simterm]
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
[/simterm]
And connect from the home IP:
[simterm]
[setevoy@setevoy-arch-pc ~] $ psql -h 18.***.***.27 -p 6666 -U username dbname Password for user username: ... dbname=>
[/simterm]
All good so far.
Create a PostgreSQL database dump
And the last thing to create a database dump.
Use the pg_dump here:
[simterm]
[setevoy@setevoy-arch-pc ~] $ pg_dump -h 18.***.***.27 -p 6666 -U username dbname > dbname.pgsql Password:
[/simterm]
Check contents:
[simterm]
[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; ...
[/simterm]
Done.
