The PSQL connect error during PostgreSQL basebackup configuration with nxs-backup typically stems from an authentication failure where the specified database user does not exist or lacks proper permissions. This issue is resolved by verifying the user's existence and updating the
pg_hba.conf file to allow the correct connection method, such as password or peer authentication, for the backup process.
Verifying Database User Existence
Before modifying configuration files, it is crucial to confirm that the database user intended for the backup operation actually exists within the PostgreSQL cluster. If the user was dropped or never created, any attempt to connect using that identity will fail immediately with a connection error. You can verify the user's presence by logging into the PostgreSQL shell using a superuser account and querying the system catalogs.
Run the following command to list all roles:
\du
Look for the specific username used in your nxs-backup configuration. If the user is missing, you must create it using the
CREATE USER or
CREATE ROLE statement with appropriate privileges. Ensure that the user has the necessary permissions to perform base backups, which often requires the
REPLICATION privilege if logical replication slots are involved, or standard read access depending on the backup tool's requirements.
Locating pg_hba.conf
The
pg_hba.conf file controls client authentication connections for PostgreSQL. It determines which users can connect from which hosts and what authentication method is required. Finding this file is the next critical step because changes here directly impact how nxs-backup authenticates against the database server. The location varies depending on your operating system and installation method.
On Debian-based systems, the file is commonly found at:
/etc/postgresql/<version>/main/pg_hba.conf
On Red Hat-based systems, it might be located at:
/var/lib/pgsql/data/pg_hba.conf
If you are unsure of the exact location, you can query the PostgreSQL server directly while connected as a superuser:
SHOW hba_file;
This command returns the full path to the active
pg_hba.conf file currently being used by the running instance. Make sure you have write permissions to this file, as root access is typically required to edit it safely.
Understanding Authentication Methods
Each line in
pg_hba.conf defines a record for matching connections. The format generally follows:
<connection-type> <database> <user> <address> <auth-method>. Common methods include
trust,
reject,
md5,
scram-sha-256, and
peer. For remote backups initiated by nxs-backup, you likely need a method that supports password-based authentication, such as
md5 or
scram-sha-256, unless you are connecting locally via Unix sockets where
peer might be sufficient.
Editing pg_hba.conf for Backup Access
To resolve the connection error, you need to add or modify a rule that allows the backup user to connect from the host running nxs-backup. Open the
pg_hba.conf file in a text editor with administrative privileges. Add a new line specifying the connection type, database, user, host address, and authentication method.
For example, to allow the user
backup_user to connect from any IP address using password authentication, add:
host all backup_user 0.0.0.0/0 md5
If the backup runs on the same machine as the database, you might use
127.0.0.1/32 for IPv4 or
::1/128 for IPv6. Alternatively, if you are using local socket connections, ensure there is a rule like:
local all backup_user scram-sha-256
Always place these rules above any catch-all
reject or
all/all entries that might block the connection. Incorrect ordering can cause earlier rules to take precedence, leading to unexpected authentication failures.
Reloading Configuration
After saving the changes to
pg_hba.conf, you must reload the PostgreSQL configuration for the changes to take effect without restarting the entire service. Use the following command to reload:
SELECT pg_reload_conf();
Or use the system utility:
sudo systemctl reload postgresql
Verify that the reload was successful by checking the PostgreSQL logs for confirmation messages. This step ensures that the new authentication rules are active and ready to accept connections from nxs-backup.
Testing the Connection
Once the configuration is updated, test the connection manually using the
psql command line tool to simulate what nxs-backup will do. Replace
backup_user with your actual username and adjust the host and port as needed:
psql -U backup_user -h 127.0.0.1 -p 5432 -d postgres
You should be prompted for the password. If the connection succeeds, the issue is resolved. If it fails, double-check the username, password, host address, and the corresponding entry in
pg_hba.conf. For more complex network issues, you might encounter different errors, such as those related to SSH key exchanges, which are distinct from database authentication problems. See our guide on troubleshooting kex_exchange_identification Error in Linux SSH Connection for related network diagnostics.
Ensuring Backup Integrity
After resolving the connection error, it is important to verify that the backup process completes successfully and that data integrity is maintained. Regularly monitor the backup logs for any warnings or errors that might indicate partial failures. Additionally, consider testing restore procedures periodically to ensure that the basebackup can be effectively used for recovery purposes. Proper configuration of
pg_hba.conf not only fixes immediate connectivity issues but also strengthens the security posture of your PostgreSQL environment by enforcing strict authentication policies.
Frequently Asked Questions
What happens if I set the authentication method to trust?
Setting the method to trust allows connections without a password, which is highly insecure for production environments. It should only be used for local development or isolated networks where security risks are minimal. Always prefer md5 or scram-sha-256 for remote connections to protect your database credentials.
Can I use peer authentication for remote nxs-backup connections?
Peer authentication relies on the operating system user matching the database user, which typically works only for local Unix socket connections. It cannot be used for TCP/IP connections from remote hosts because the OS user context is not available across network boundaries. Use password-based methods like md5 for remote backup agents.
How do I find the PostgreSQL version if I am unsure?
You can determine the version by running the command
psql --version in the terminal or by executing
SELECT version(); within the psql interface. Knowing the version helps you locate the correct directory structure for
pg_hba.conf and understand supported authentication methods.
Why does my backup still fail after reloading pg_hba.conf?
If the connection fails after reloading, check if the firewall is blocking the port or if the nxs-backup service is using the wrong credentials. Also, verify that the rule in
pg_hba.conf matches the exact host address and user name specified in your backup configuration. Misaligned parameters are a common cause of persistent connection errors.
Is it safe to restart the PostgreSQL service instead of reloading?
Restarting the service applies changes but causes a brief downtime, interrupting active queries and connections. Reloading is preferred because it updates configuration files dynamically without disrupting existing sessions. Use restarts only if reloading fails or if you change settings that require a full restart, such as shared memory parameters.