Please help me restore a postgres database

Help
1

TLDR: I need help finding the specific set of commands that will enable me to restore a postgres database.

I’m using a very basic configuration to setup Home Assistant along with a postgres database, both running as a nixos module, and I also am using the automated postgres database backup service. It all looks like this:

{ 
  config, 
  pkgs, 
  ... 
}: 

{

  networking.firewall.allowedTCPPorts = [ 8123 ];

  services = {

    home-assistant = {
      enable = true;
      package = (pkgs.home-assistant.override {
        extraPackages = py: with py; [ psycopg2 ];
        }).overrideAttrs (oldAttrs: {
          doInstallCheck = false;
        });
      extraComponents = [
        "default_config"
      ];
      config = {
        http.server_port = 8123;
        recorder.db_url = "postgresql://@/hass";
      };
    };

    postgresql = {
      enable = true;
      package = pkgs.postgresql_17;
      ensureDatabases = [ "hass" ];
      ensureUsers = [
        {
          name = "hass"; 
          ensureDBOwnership = true;
        }
      ];
    };

    postgresqlBackup = { # backup the "hass" database daily at 1am
      enable = true;
      databases = [ "hass" ];
      startAt = "*-*-* 01:00:00";
    };

  };

}

The automated backup creates a file called hass.sql.gz, which can then be unzipped to hass.sql.

I’ve been wildly unsuccessful at being able to actually restore this database though. I’ve run into errors using pg_restore due to it complaining the file is not a proper postgres archive as well as endless permissions errors trying many combinations of psql to restore it. Can anyone help me?

Here are a bunch of things that don’t work right now:

pg_restore -U hass -d hass --single-transaction hass.sql # input file appears to be a text format dump. Please use psql.
psql -U hass -d template0 -c "DROP DATABASE \"hass\";" # peer authentication failed for user hass
psql -U postgres -d template0 -c "DROP DATABASE \"hass\";" # peer authentication failed for user postgres
psql -U hass -d template0 -c "CREATE DATABASE \"hass\";" # peer authentication failed for user hass
psql -U postgres -d template0 -c "CREATE DATABASE \"hass\";" # peer authentication failed for user postgres
createdb -U hass -d hass -f hass.sql.gz # peer authentication failed for user hass
createdb -U postgres -d hass -f hass.sql.gz # peer authentication failed for user postgres
2

I think you are directionally correct by using psql to basically run the SQL statements from the backup dump.

You need to fix the peer authentication failed error first. What it’s saying is that postgres does not like the way you’re trying to access it from your current location (doc on auth methods).

You can override postgres auth using services.postgresql.authentication nixos option. That sets the content of pg_hba.conf.

The nuclear option could be a variation of

host all all 172.0.0.0/8 trust

Which allows anyone from the local machine to log in as any user. Set it, make the changes, and don’t forget to remove it.

1 Like
3

When running a command as psql -U hass you will get a peer authentication error unless you are the running it from the hass user. You need to do sudo -u hass psql -U hass. What was suggested before will work, if you switch psql to TCP, which isn’t the default, can be done with psql -U hass -h 127.0.0.1

1 Like
4

Right, I keep forgetting that listening only on the socket is the default. IIRC there’s also the cheat code of:

su - postgres
psql # no params, run as postgres Linux user
5

I was successfully able to drop and restore a clean hass database per prior suggestions of doing so as the system hass user:

sudo -u postgres psql -U postgres -d template1 -c "DROP DATABASE \"hass\";"

and

sudo -u postgres psql -U postgres -d template1 -c "CREATE DATABASE \"hass\" OWNER \"hass\";"

However when trying to restore the database with sudo -u hass psql -U hass -d hass -f hass.sql, I’m getting the error psql: error: hass.sql: Permission denied.

I checked the database user details with sudo -u postgres psql -c "\du" and see that the hass user created in my nix module does not have any permissions:

List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 hass      |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

I feel like I’m getting closer but am not sure if this is the problem or how to advance from here…

6

hass.sql: Permission denied.

Is a complaint about permissions of the file. Move that file to a location where the Linux user you’re switching to has access and check the files permissions. Change them using chmod if necessary.

7

Okay, file permissions fixed and it seems to be working, although I’m still getting an error about the database already existing:

SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
psql:/var/lib/postgresql/hass.sql:23: ERROR:  database "hass" already exists
ALTER DATABASE
You are now connected to database "hass" as user "postgres".
SET
(seemingly goes on successfully from here)

So for reference, complete sequence at this point is:
sudo systemctl stop home-assistant.service
sudo -u postgres psql -U postgres -d template1 -c "DROP DATABASE \"hass\";"
sudo -u postgres psql -U postgres -d template1 -c "CREATE DATABASE \"hass\" OWNER \"hass\";"
sudo -u postgres psql -U postgres -d hass -f hass.sql
sudo systemctl start home-assistant.service

Is the error anything to be concerned about? It appears to restore the database and everything seems to be working with the restored content…

Thanks!

8

It’s probably fine. Often SQL is being executed in a way that will not abort the sequence of statements if one of them failed (CREATE DATABASE failed but the rest of the steps will be executed). This is probably controlled by some psql flag.

SQL can be written in a more idempotent way (CREATE DATABASE IF NOT EXISTS foo will not fail if the database exists) but IIRC that’s not technically part of ANSI SQL standard, rather a very common SQL extension. The dump is probably created in a way that’s more compatible with the standard.