Initialization of the db

[ftomassetti]

I tried launching the repository with the new configuration file.

Initially I got an error message stating that the currently open DB (called cis) could not be dropped. So I connected to the postgres server and deleted the DB manually. Then I re-launched the command and now I get this message:

> @lionweb/repository-server@0.2.0 dev
> nodemon dist/server.js --config

[nodemon] 3.0.1
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: js,mjs,cjs,json
[nodemon] starting `node dist/server.js`
/Users/ftomassetti/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:283
        const message = name === 'notice' ? new messages_1.NoticeMessage(length, messageValue) : new messages_1.DatabaseError(messageValue, length, name);
                                                                                                 ^

error: database "cis" does not exist
    at Parser.parseErrorMessage (/Users/ftomassetti/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:283:98)
    at Parser.handlePacket (/Users/ftomassetti/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:122:29)
    at Parser.parse (/Users/ftomassetti/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:35:38)
    at Socket.<anonymous> (/Users/ftomassetti/repos/lionweb-repository/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 89,
  severity: 'FATAL',
  code: '3D000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postinit.c',
  line: '1019',
  routine: 'InitPostgres'
}

Node.js v18.14.0
[nodemon] app crashed - waiting for file changes before starting...

Maybe this is expected, and we should create the DB before starting the server. In #61 it was proposed to add the flag --create-database, but I think it is not yet there.

Maybe I should call POST /createRepository, but I cannot as the server fails at starting time

[ftomassetti]

Maybe we should use a differently configured connection to run the create database statement

[joswarmer]

There is a config option in the server-config.json file to create the database, did you try this?

[ftomassetti]

Yes, and the part creating the DB seems to be causing this issue

[ftomassetti]

I am doing some experiments, and I seem to get a different error if I change a couple of things:

export const CREATE_CONFIG: PostgresConfig = {
    host: ServerConfig.getInstance().pgHost(),
    port: ServerConfig.getInstance().pgPort(),
    user: ServerConfig.getInstance().pgUser(),
    password: ServerConfig.getInstance().pgPassword(),
    database: "postgres", // added this
    ssl: pgSSLConf // and this: null in this case, but it could be potentially useful
}

Changed:

    async queryWithoutRepository(query: string) {
        return await this.dbConnection.query(query)
    }

Into:

    async queryWithoutRepository(query: string) {
        return await this.postgresConnection.query(query)
    }

[ftomassetti]

Ok, If I do these changes I can then get the database created (it then crash later):

CREATE DATABASE ${ServerConfig.getInstance().pgDb()}
    WITH
    OWNER = '${ServerConfig.getInstance().pgUser()}'
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8' // change it to en_US.UTF-8
    LC_CTYPE = 'en_US.utf8' // change it to en_US.UTF-8
    LOCALE_PROVIDER = 'libc'
    TABLESPACE = pg_default // I remove this line, so that it defaults to pg_default anyway but with this it fails. See: https://dba.stackexchange.com/questions/204807/why-do-i-get-a-postgresql-permission-error-when-specifying-a-tablespace-in-the 
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

[joswarmer]

Maybe we should use a differently configured connection to run the create database statement

That is actually done using the postgressConnection in DbAdminApiWorker.ts on line 72

[joswarmer]

Could you send your original config file that went wrong? Would like to double-check whether that is ok

[ftomassetti]

I can reproduce the issue also with the newest version.

There is a small issue with passing the config parameter (I will send a PR for that). Once that is out of the way, I can still see the query for creating the DB fail.

The configuration (redacted) is:

{
  "server": {
    "serverPort": 3005,
    "expectedToken": "YYYYYYYY",
    "bodyLimit": "500mb"
  },
  "startup": {
    "createDatabase": true,
    "createRepositories": [
      {
        "name": "default",
        "history": false
      }    ]
  },
  "logging": {
    "request": "info",
    "database": "silent",
    "express": "silent"
  },
  "postgres": {
    "database": {
      "host": "localhost",
      "user": "cis",
      "db": "cis",
      "password": "XXXXXXXX",
      "port": "5432"
    },
    "certificates": {
      "rootcert": null,
      "rootcertcontent": null
    }
  }
}

When starting the server, it attempts creating the database but that fail:

/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:283                                                          │
        const message = name === 'notice' ? new messages_1.NoticeMessage(length, messageValue) : new messages_1.DatabaseError(messageVal│
ue, length, name);                                                                                                                      │
                                                                                                 ^                                      │
                                                                                                                                        │
error: database "cis" does not exist                                                                                                    │
    at Parser.parseErrorMessage (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:283:98)                     │
    at Parser.handlePacket (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:122:29)                          │
    at Parser.parse (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:35:38)                                  │
    at Socket.<anonymous> (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/index.js:11:42)                             │
    at Socket.emit (node:events:517:28)                                                                                                 │
    at addChunk (node:internal/streams/readable:368:12)                                                                                 │
    at readableAddChunk (node:internal/streams/readable:341:9)                                                                          │
    at Readable.push (node:internal/streams/readable:278:10)                                                                            │
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {                                                                    │
  length: 89,                                                                                                                           │
  severity: 'FATAL',                                                                                                                    │
  code: '3D000',                                                                                                                        │
  detail: undefined,                                                                                                                    │
  hint: undefined,                                                                                                                      │
  position: undefined,                                                                                                                  │
  internalPosition: undefined,                                                                                                          │
  internalQuery: undefined,                                                                                                             │
  where: undefined,                                                                                                                     │
  schema: undefined,                                                                                                                    │
  table: undefined,                                                                                                                     │
  column: undefined,                                                                                                                    │
  dataType: undefined,                                                                                                                  │
  constraint: undefined,                                                                                                                │
  file: 'postinit.c',                                                                                                                   │
  line: '1019',                                                                                                                         │
  routine: 'InitPostgres'                                                                                                               │
}

(sorry for the extra pipe characters, I am copying the text from a console splitted in half).

If I change the CREATE_CONFIG to specify the postgres database, I get a different error about LC_COLLATE, but I think I get over this one:

export const CREATE_CONFIG: PostgresConfig = {
    host: ServerConfig.getInstance().pgHost(),
    port: ServerConfig.getInstance().pgPort(),
    user: ServerConfig.getInstance().pgUser(),
    password: ServerConfig.getInstance().pgPassword(),
    database: "postgres" // ADDED
}

My understanding of the issue is that creating a database means operating on the maintenance database. If we do not specify a database, we connect by default to the database named after the user (cis in this case). However, in this case we should connect to the maintenance database (postgres) in order to create the database. So I think that CREATE_CONFIG would work only when the username is the same as the maintenance database. That is typically the case for the user postgres.

So I think we may need to add an extra parameter to the configuration, called maintenanceDb, which would default to postgres. We should then use that value in CREATE_CONFIG.

[ftomassetti]

If I then do the change specified above I get the following error:

error: new collation (en_US.utf8) is incompatible with the collation of the template database (en_US.UTF-8)                             │
    at Parser.parseErrorMessage (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:283:98)                     │
    at Parser.handlePacket (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:122:29)                          │
    at Parser.parse (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:35:38)                                  │
    at Socket.<anonymous> (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/index.js:11:42)                             │
    at Socket.emit (node:events:517:28)                                                                                                 │
    at addChunk (node:internal/streams/readable:368:12)                                                                                 │
    at readableAddChunk (node:internal/streams/readable:341:9)                                                                          │
    at Readable.push (node:internal/streams/readable:278:10)                                                                            │
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {                                                                    │
  length: 241,                                                                                                                          │
  severity: 'ERROR',                                                                                                                    │
  code: '22023',                                                                                                                        │
  detail: undefined,                                                                                                                    │
  hint: 'Use the same collation as in the template database, or use template0 as template.',                                            │
  position: undefined,                                                                                                                  │
  internalPosition: undefined,                                                                                                          │
  internalQuery: undefined,                                                                                                             │
  where: undefined,                                                                                                                     │
  schema: undefined,                                                                                                                    │
  table: undefined,                                                                                                                     │
  column: undefined,                                                                                                                    │
  dataType: undefined,                                                                                                                  │
  constraint: undefined,                                                                                                                │
  file: 'dbcommands.c',                                                                                                                 │
  line: '1134',                                                                                                                         │
  routine: 'createdb',                                                                                                                  │
  query: '\n' +                                                                                                                         │
    'CREATE DATABASE cis\n' +                                                                                                           │
    '    WITH\n' +                                                                                                                      │
    "    OWNER = 'cis'\n" +                                                                                                             │
    "    ENCODING = 'UTF8'\n" +                                                                                                         │
    "    LC_COLLATE = 'en_US.utf8'\n" +                                                                                                 │
    "    LC_CTYPE = 'en_US.utf8'\n" +                                                                                                   │
    "    LOCALE_PROVIDER = 'libc'\n" +                                                                                                  │
    '    TABLESPACE = pg_default\n' +                                                                                                   │
    '    CONNECTION LIMIT = -1\n' +                                                                                                     │
    '    IS_TEMPLATE = False',                                                                                                          │
  params: undefined                                                                                                                     │
}

This issue disappears if I change these two lines:

    LC_COLLATE = 'en_US.UTF-8' // was en_US.utf8
    LC_CTYPE = 'en_US.UTF-8' // was en_US.utf8

And this leads to get a different problem... :D

[ftomassetti]

The next problem is now about tablespace:

error: permission denied for tablespace pg_default                                                                                      │
    at Parser.parseErrorMessage (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:283:98)                     │
    at Parser.handlePacket (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:122:29)                          │
    at Parser.parse (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/parser.js:35:38)                                  │
    at Socket.<anonymous> (/home/cis/repos/lionweb-repository/node_modules/pg-protocol/dist/index.js:11:42)                             │
    at Socket.emit (node:events:517:28)                                                                                                 │
    at addChunk (node:internal/streams/readable:368:12)                                                                                 │
    at readableAddChunk (node:internal/streams/readable:341:9)                                                                          │
    at Readable.push (node:internal/streams/readable:278:10)                                                                            │
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {                                                                    │
  length: 103,                                                                                                                          │
  severity: 'ERROR',                                                                                                                    │
  code: '42501',                                                                                                                        │
  detail: undefined,                                                                                                                    │
  hint: undefined,                                                                                                                      │
  position: undefined,                                                                                                                  │
  internalPosition: undefined,                                                                                                          │
  internalQuery: undefined,                                                                                                             │
  where: undefined,                                                                                                                     │
  schema: undefined,                                                                                                                    │
  table: undefined,                                                                                                                     │
  column: undefined,                                                                                                                    │
  dataType: undefined,                                                                                                                  │
  constraint: undefined,                                                                                                                │
  file: 'aclchk.c',                                                                                                                     │
  line: '2807',                                                                                                                         │
  routine: 'aclcheck_error',                                                                                                            │
  query: '\n' +                                                                                                                         │
    'CREATE DATABASE cis\n' +                                                                                                           │
    '    WITH\n' +                                                                                                                      │
    "    OWNER = 'cis'\n" +                                                                                                             │
    "    ENCODING = 'UTF8'\n" +                                                                                                         │
    "    LC_COLLATE = 'en_US.UTF-8'\n" +                                                                                                │
    "    LC_CTYPE = 'en_US.UTF-8'\n" +                                                                                                  │
    "    LOCALE_PROVIDER = 'libc'\n" +                                                                                                  │
    '    TABLESPACE = pg_default\n' +                                                                                                   │
    '    CONNECTION LIMIT = -1\n' +                                                                                                     │
    '    IS_TEMPLATE = False',                                                                                                          │
  params: undefined                                                                                                                     │
}

This problem can be solved simply by removing the explicit reference to tablespace. According to this:

Here's the trick, at least with pgAdmin v4: Leave the tablespace blank. It will default to "pg_default" when creating the database.

With this change I can create the database!

[nodemon] 3.0.1                                                                                                                         │
[nodemon] to restart at any time, enter `rs`                                                                                            │
[nodemon] watching path(s): *.*                                                                                                         │
[nodemon] watching extensions: js,mjs,cjs,json                                                                                          │
[nodemon] starting `node dist/server.js --config ../../../XXXXX/lwrepo-conf/server-config.json`                               │
About to create database                                                                                                                │
create database - not done                                                                                                              │
INFO: Registering DB Admin Module                                                                                                       │
INFO: Registering Bulk API Module                                                                                                       │
INFO: Registering Inspection Module                                                                                                     │
INFO: Registering Additional API Module                                                                                                 │
INFO: Registering Additional API Module                                                                                                 │
INFO: Registering History API Module                                                                                                    │
create database - about to query                                                                                                        │
create database - query executed                                                                                                        │
INFO: Server is running at port 3005 =========================================================

[joswarmer]

Wow!

[joswarmer]

The reason for not having a database in the CREATE_CONFIG is that it is used to create a database, therefore it doesn't need a database connection but a postgres connection without database. The [database cis](error: database "cis" does not exist) is most probably because of one of

1. the DROP DATABASE IF EXISTS cis WITH (FORCE); query. Don't understand why it is sometimes a problem (in your postgres setup) and sometimes not (my postgres setup) , or
2. creating the repository specified in the config file in the 'cis' database. In this case I would have expected that the database creating has failed and would give an error.

You could try the original setup with an empty createRepositories (case 2 won't happen) to see whether the database creation query is the one failing.

[ftomassetti]

1. the DROP DATABASE IF EXISTS cis WITH (FORCE); query. Don't understand why it is sometimes a problem (in your postgres setup) and sometimes not (my postgres setup) , or

If there was an issue due to drop database requiring the database to be present (basically ignoring the IF EXISTS part), then I would expect it to fail, even when I specify postgres as the connection database for this query

2. creating the repository specified in the config file in the 'cis' database. In this case I would have expected that the database creating has failed and would give an error.

I am not sure I understand this point. I think the database creation fails, so we do not arrive to the point where we try to create the repository within the database

The reason for not having a database in the CREATE_CONFIG is that it is used to create a database, therefore it doesn't need a database connection but a postgres connection without database.

I found this resource stating:

You have to connect to a database. Which database you could use for a "maintenance database" depends on the installation and the subsequent administration.

Another answer states:

As the manual of pg_connect says the dbname parameter in the connection string defaults to the value of the user parameter.

I tried looking up the manual of pgPromise but I did not find a definitive answer there.

I also verified that if I run from the CLI:

// Here I do not specify the database
psql -d postgresql://cis:<my password>@localhost:5432 -c "DROP DATABASE IF EXISTS cis WITH (FORCE)"
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  database "cis" does not exist

// Here I specify the postgres database
psql -d postgresql://cis:<my password>@localhost:5432/postgres -c "DROP DATABASE IF EXISTS cis WITH (FORCE)"
DROP DATABASE

It succeeds specifying the database and fails without

[joswarmer]

Ok, looks like we need to specify the admin database indeed.

[ftomassetti]

I also tried this:

// at this time database cis exists
psql -d postgresql://cis:<password>@localhost:5432 -c "DROP DATABASE IF EXISTS foo WITH (FORCE)"
NOTICE:  database "foo" does not exist, skipping
DROP DATABASE

But if I delete the cis database and try again:

psql -d postgresql://cis:<password>@localhost:5432 -c "DROP DATABASE IF EXISTS foo WITH (FORCE)"
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  database "cis" does not exist

Even if I am trying to delete foo it is complaining about cis not existing

[joswarmer]

Trying to run this branch locally. Interstuingly you get error: new collation (en_US.utf8) is incompatible with the collation of the template database (en_US.UTF-8) while I get error: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf8)│ Exactly the opposite :-). It seems that this is something that is set when creating the postgres instance.

See https://stackoverflow.com/questions/18870775/how-to-change-the-template-database-collection-coding or https://dba.stackexchange.com/questions/311068/collation-wont-set-when-creating-new-database

[joswarmer]

I also tried this:

// at this time database cis exists
psql -d postgresql://cis:<password>@localhost:5432 -c "DROP DATABASE IF EXISTS foo WITH (FORCE)"
NOTICE:  database "foo" does not exist, skipping
DROP DATABASE

But if I delete the cis database and try again:

psql -d postgresql://cis:<password>@localhost:5432 -c "DROP DATABASE IF EXISTS foo WITH (FORCE)"
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  database "cis" does not exist

Even if I am trying to delete foo it is complaining about cis not existing This might be because the connection string uses cis in postgresql://cis:<password>@localhost:5432 and you cannot connect to the database specified.

[joswarmer]

Adding TEMPLATE = template0 in create-database-sql.ts solves the collation error.