Hello, this is my help for others who are reading this book and trying to import the data.

[kundeng]

Context

• Documentation Assumption: PostgreSQL docs and dumps often assume the default superuser is "postgres", so dumps reference roles and ownership accordingly.
• Your Environment: To match the dump’s expectations, you created roles like "sqldaadmin" and "rdsadmin". The cleaner approach is to use sqldaadmin as your de facto superuser (granting it privileges like creating databases) in place of postgres.

---

1. Error: Role "sqldaadmin" does not exist

• Cause: The dump references a role that hasn’t been created.
• Solution: Create the role and grant it superuser privileges (or at least rights like CREATEDB):

  CREATE ROLE sqldaadmin WITH LOGIN PASSWORD 'your_password';
  ALTER ROLE sqldaadmin WITH SUPERUSER;

---

2. Granting Admin Rights to the "sqlda" Database

• Options:
  • Change Ownership:

    ALTER DATABASE sqlda OWNER TO sqldaadmin;

  • Grant All Privileges:

    GRANT ALL PRIVILEGES ON DATABASE sqlda TO sqldaadmin;

---

3. Remote Login Issue for "sqldaadmin"

• Cause: While local login works (confirming the password is correct), remote login fails if the stored password encryption doesn’t match the pg_hba.conf method (e.g., stored as SCRAM-SHA-256 vs. expected MD5).
• Solutions:
  • Option B (Easier): Force MD5 password storage by resetting the password:

    SET password_encryption = 'md5';
    ALTER ROLE sqldaadmin WITH PASSWORD 'your_desired_password';

  • Option A: Alternatively, update pg_hba.conf to use the matching authentication method:

    host    all    all    all    scram-sha-256
    

    Then reload PostgreSQL’s configuration.

---

4. Error: Cannot Drop Database Because It’s Open

• Cause: Active connections are preventing the drop.
• Solutions:
  • Terminate Active Connections: Connect to another database (e.g., postgres or any other) and run:

    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = 'sqlda'
      AND pid <> pg_backend_pid();

  • Drop the Database:

    DROP DATABASE sqlda;

  • Alternative (PostgreSQL 13+): Use the force option:

    DROP DATABASE sqlda WITH (FORCE);

---

5. Error: Role "rdsadmin" does not exist

• Cause: The dump references another missing role.
• Solution: Create the role:

  CREATE ROLE rdsadmin WITH LOGIN;

---

Bottom Line:
Since your dump was created in an environment assuming the default superuser is "postgres", you must manually create the missing roles and grant them the necessary privileges. Using sqldaadmin as your superuser (with rights like CREATEDB) and aligning your password encryption with pg_hba.conf will resolve the issues for both local and remote connections, ensuring a smooth dump reimport.