PostgreSQL as LakeXpress DB

1. Create the database

Run as a PostgreSQL administrator (for example postgres):

CREATE USER lakexpress_admin WITH PASSWORD 'your_password';
CREATE DATABASE lakexpress_log OWNER lakexpress_admin;

LakeXpress creates its tables in the public schema. Making lakexpress_admin the owner of the database gives it the right to create them. GRANT ALL PRIVILEGES ON DATABASE alone is not enough on PostgreSQL 15 and later.

2. Add it to the credentials file

{
  "lxdb_postgres": {
    "ds_type": "postgres",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_LXDB_USER}",
      "password": "$env{LX_LXDB_PASSWORD}",
      "server": "pg.company.com",
      "port": 5432,
      "database": "lakexpress_log"
    }
  }
}

The connection parameters are the same as for a PostgreSQL source (SSL mode, connection string): see PostgreSQL connection parameters.

3. Create the LakeXpress tables

./LakeXpress lxdb init -a credentials.json --lxdb_auth_id lxdb_postgres

Troubleshooting

permission denied for schema public: The user can connect but cannot create tables. Make it the owner of the database (ALTER DATABASE lakexpress_log OWNER TO lakexpress_admin;), or grant CREATE on the schema: GRANT CREATE ON SCHEMA public TO lakexpress_admin; (run while connected to lakexpress_log).

Connection refused or authentication failed: See the PostgreSQL troubleshooting section.