[FEAT]: Support specifying a Postgres Schema other than public #1945
Comments
|
@doctorpangloss A change have been added to support to the schema. It will be part of the next release Thank you for requesting this change |
|
@vincent-pochet , I'm sorry if that's the wrong channel for that - please, correct me if so. It seems like I'm having issues with this working - my api container is failing. I have the following line at my - DATABASE_URL=${DATABASE_URL}And
API container logs ( ESC[1mUnrecognized command "db:migrate:primary" (ESC[1;4mRails::Command::UnrecognizedCommandErrorESC[mESC[1m)ESC[m
ESC[1mDid you mean? db:migrate:upESC[m
=> Booting Puma
=> Rails 7.1.3.4 application starting in production
=> Run `bin/rails server --help` for more startup options
Puma starting in single mode...
* Puma version: 6.4.2 (ruby 3.3.0-p0) ("The Eagle of Durango")
* Min threads: 0
* Max threads: 5
* Environment: production
* PID: 44
* Listening on http://[::]:3000
Use Ctrl-C to stop
I, [2024-07-03T18:15:07.983771 #44] INFO -- : [6f2a23b5-ddfe-4239-bd32-9b5040ccc821] {"method":"GET","path":"/health","format":"*/*","controller":"ApplicationController","action":"health","status":200,"allocations":47303,"duration":168.61,"view":0.52,"db":40.04,"ddsource":"ruby","params":{},"organization_id":null}And indeed if I try to run the migration manually - it's failing - please, find below the logs from within Docker lago-api container: # docker exec -it lago-api "/bin/sh"
# ps aufx
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 124 0.1 0.0 2580 888 pts/0 Ss 18:15 0:00 /bin/sh
root 146 0.0 0.1 8484 4136 pts/0 R+ 18:15 0:00 \_ ps aufx
root 1 0.0 0.0 4348 3236 ? Ss 18:14 0:00 /bin/bash ./scripts/start.sh
root 44 10.6 4.9 1556516 195136 ? Sl 18:15 0:05 puma 6.4.2 (tcp://[::]:3000) [app]
# bundle exec rails db:migrate:primary
Unrecognized command "db:migrate:primary" (Rails::Command::UnrecognizedCommandError)
Did you mean? db:migrate:upMy previous Lago installation - on a separate database - worked just fine, so it seems like schema change might be the one to blame... I'm really lost - it doesn't feel like migration could gone missing... 😳 |
@alexander-potemkin could you try the following command in the API container bundle exec rails db:migrate # without the :primaryAnd restart the application It does not seems related directly to the change regarding the schema, but to the migrate command in the |
|
@vincent-pochet , thanks! Now it execute the code, but it seems like ignoring schema nevertheless: # bundle exec rails db:migrate
bin/rails aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied for schema public (ActiveRecord::StatementInvalid)
LINE 1: CREATE TABLE "schema_migrations" ("version" character varyin...
^
Caused by:
PG::InsufficientPrivilege: ERROR: permission denied for schema public (PG::InsufficientPrivilege)
LINE 1: CREATE TABLE "schema_migrations" ("version" character varyin...
^
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
env | grep -i database
DATABASE_URL=postgresql://MyUser:MyPassword@MyHost:6543/postgres?search_path=lago |
|
... and just in case - api worker is not affected, right? It doesn't seem to be: $ cat start.worker.sh
#!/bin/bash
bundle exec sidekiq -C config/sidekiq.ymlBut I though to ask, just in case. |
@alexander-potemkin 🤔 . In this case the |
@vincent-pochet , thank you! Hope it will be all resolved! Yeah, it does... I tried to check the Lago code yesterday, as it felt like there might be some parts of the code that will just ignore canonical postgresql:// url schema, especially since Please, let me know if any additional information would be required! |
|
I found the issue @alexander-potemkin For some reason, Rails is ignoring the A pull request is open at https://github.com/getlago/lago-api/pull/2246/files with a fix. It will certainly be part of the next release. To use it and fix your issue, you will have to set the |
Thank you, @vincent-pochet ! It seems, however, something else is missing - please, see the output from within lago-api container below: # ps aufx
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 100 0.0 0.0 2580 892 pts/0 Ss 19:32 0:00 /bin/sh
root 186 0.0 0.1 8488 4124 pts/0 R+ 19:33 0:00 \_ ps aufx
root 1 0.0 0.0 4348 3192 ? Ss 19:31 0:00 /bin/bash ./scripts/start.sh
root 43 6.6 5.1 1574052 204116 ? Sl 19:31 0:05 puma 6.4.2 (tcp://[::]:3000) [app]
# env | grep POSTGRES_SCHEMA
POSTGRES_SCHEMA=lago
# bundle exec rails db:migrate
bin/rails aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied for schema public (ActiveRecord::StatementInvalid)
LINE 1: CREATE TABLE "schema_migrations" ("version" character varyin...
^
Caused by:
PG::InsufficientPrivilege: ERROR: permission denied for schema public (PG::InsufficientPrivilege)
LINE 1: CREATE TABLE "schema_migrations" ("version" character varyin...
^
Tasks: TOP => db:migrate
(See full trace by running task with --trace) |
|
I've applied the config yaml changes from the commit as well manually, inside the container, as it felt appropriate, but it doesn't seem to resolve things: root@370a0651199e:/app# grep -i schema_search_path config/database.yml
schema_search_path: "public"
schema_search_path: <%= ENV.fetch('POSTGRES_SCHEMA', 'public') %>
root@370a0651199e:/app# bundle exec rails db:migrate
bin/rails aborted!
ActiveRecord::StatementInvalid: PG::InvalidSchemaName: ERROR: no schema has been selected to create in (ActiveRecord::StatementInvalid)
LINE 1: CREATE TABLE "schema_migrations" ("version" character varyin...
^
Caused by:
PG::InvalidSchemaName: ERROR: no schema has been selected to create in (PG::InvalidSchemaName)
LINE 1: CREATE TABLE "schema_migrations" ("version" character varyin...
^
Tasks: TOP => db:migrate
(See full trace by running task with --trace) |
|
And just in case - I've hardcoded variables at the config file, but it doesn't seem to help: root@370a0651199e:/app# bundle exec rails db:migrate
bin/rails aborted!
ActiveRecord::StatementInvalid: PG::InvalidSchemaName: ERROR: no schema has been selected to create in (ActiveRecord::StatementInvalid)
LINE 1: CREATE TABLE "schema_migrations" ("version" character varyin...
^
Caused by:
PG::InvalidSchemaName: ERROR: no schema has been selected to create in (PG::InvalidSchemaName)
LINE 1: CREATE TABLE "schema_migrations" ("version" character varyin...
^
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
root@370a0651199e:/app# grep -i schema_search_path config/database.yml
schema_search_path: lago
schema_search_path: lago |
|
@vincent-pochet , if the things works on your side - could you possibly share the configuration you have (with private data redacted)? Or I can provide you mine, including full database access if required for the testing? If something of that could work or there are any other ways to help (including avoid to disturb you) - please, let me know! |
## Context This PR is a fix for #1945 (comment) ## Description Lago startup script is relying on the `rails db:migrate:primary` to perform the migration at startup. Since in some situation, only one database is configured in the `database.yml` file, the command is failing with the following error: ``` Unrecognized command "db:migrate:primary" (Rails::Command::UnrecognizedCommandError) Did you mean? db:migrate:up ``` This PR fixes this by using `rails db:migrate` instead when only one database is present.
Hello @alexander-potemkin I tried on a local setup. I first created a schema in Postgres and set the its name to the rails db:create
rails db:migrateAfter a check in Postgres console, all the table were created. What version of Lago are you using? Also, just to be sure, did you restarted the app after the update of the database config flle ? |
Thank you, @vincent-pochet ! Restarted the container, dumped config from there, tried the commands you offered - it failed: root@370a0651199e:/app# env | grep -i schema
POSTGRES_SCHEMA=lago
root@370a0651199e:/app# rails db:create
PG::InsufficientPrivilege: ERROR: permission denied to create database
Couldn't create 'postgres' database. Please check your configuration.
bin/rails aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied to create database (ActiveRecord::StatementInvalid)
Caused by:
PG::InsufficientPrivilege: ERROR: permission denied to create database (PG::InsufficientPrivilege)
Tasks: TOP => db:create
(See full trace by running task with --trace)
root@370a0651199e:/app# rails db:migrate
bin/rails aborted!
ActiveRecord::StatementInvalid: PG::InvalidSchemaName: ERROR: no schema has been selected to create in (ActiveRecord::StatementInvalid)
LINE 1: CREATE TABLE "schema_migrations" ("version" character varyin...
^
Caused by:
PG::InvalidSchemaName: ERROR: no schema has been selected to create in (PG::InvalidSchemaName)
LINE 1: CREATE TABLE "schema_migrations" ("version" character varyin...
^
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
root@370a0651199e:/app# grep -i schema_search_path config/database.yml
schema_search_path: "public"
schema_search_path: <%= ENV.fetch('POSTGRES_SCHEMA', 'public') %>
root@370a0651199e:/app# cat LAGO_VERSION
v1.6.0Lago version is the latest, from what I can see. Database can't be created - that's Ok, as it's created already; but migration fails as no schema provided - probably that's the cause? And just in case - when the changes you applied shall be available? Is there some option for a hotfix release, if that's not too much to ask? |
|
I believe that I have found an issue on my side - database permissions were not sufficient - that is to be fixed with the following SQL statements: GRANT USAGE ON SCHEMA lago TO lago;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA lago TO lago;
GRANT ALL ON ALL SEQUENCES IN SCHEMA lago TO lago;
GRANT ALL ON AL FUNCTIONS IN SCHEMA lago TO lago;
GRANT ALL ON SCHEMA lago TO lago
I tried to register and encountered another error, which is, I believe, somehow related - despite error points to a different direction: It feels that something is wrong with the logic maybe - as it feels like db::create has to be executed first - to create database with all tables required (if it works the way I think it should), but db::create fails, as it tries to create database that already exists (and I can't Then db::migrate runs successfully (not a single error), but it seems like fails to create all tables required. Any clue would be much appreciated! |
|
To make sure things are isolated as much as possible, I created new schema, brought docker cluster down and then up (applying 'patch' on database config) and here is what I have when I try to do migration from scratch: root@38c541a217ca:/app/scripts# bundle exec rails db:migrate
I, [2024-07-05T19:58:50.010522 #131] INFO -- : Migrating to InitSchema (20220525122759)
== 20220525122759 InitSchema: migrating =======================================
-- enable_extension("pgcrypto")
-> 0.0182s
-- create_table("add_ons", {:id=>:uuid, :default=>#<Proc:0x00007f17a0a50d48 /app/db/migrate/20220525122759_init_schema.rb:7 (lambda)>, :force=>:cascade})
-> 0.0239s
-- create_table("applied_coupons", {:id=>:uuid, :default=>#<Proc:0x00007f17a07f5be8 /app/db/migrate/20220525122759_init_schema.rb:20 (lambda)>, :force=>:cascade})
-> 0.0436s
-- create_table("billable_metrics", {:id=>:uuid, :default=>#<Proc:0x00007f17a0860420 /app/db/migrate/20220525122759_init_schema.rb:37 (lambda)>, :force=>:cascade})
-> 0.0289s
-- create_table("charges", {:id=>:uuid, :default=>#<Proc:0x00007f17a089bea8 /app/db/migrate/20220525122759_init_schema.rb:51 (lambda)>, :force=>:cascade})
-> 0.0316s
-- create_table("coupons", {:id=>:uuid, :default=>#<Proc:0x00007f17a0895710 /app/db/migrate/20220525122759_init_schema.rb:63 (lambda)>, :force=>:cascade})
-> 0.0357s
-- create_table("credits", {:force=>:cascade})
-> 0.0272s
-- create_table("customers", {:id=>:uuid, :default=>#<Proc:0x00007f17a02c4ea8 /app/db/migrate/20220525122759_init_schema.rb:93 (lambda)>, :force=>:cascade})
-> 0.0322s
-- create_table("events", {:id=>:uuid, :default=>#<Proc:0x00007f17a0268798 /app/db/migrate/20220525122759_init_schema.rb:116 (lambda)>, :force=>:cascade})
-> 0.0432s
-- create_table("fees", {:id=>:uuid, :default=>#<Proc:0x00007f17a0260d40 /app/db/migrate/20220525122759_init_schema.rb:133 (lambda)>, :force=>:cascade})
-> 0.0366s
-- create_table("invoices", {:id=>:uuid, :default=>#<Proc:0x00007f17a021a048 /app/db/migrate/20220525122759_init_schema.rb:150 (lambda)>, :force=>:cascade})
-> 0.0374s
-- create_table("memberships", {:id=>:uuid, :default=>#<Proc:0x00007f17a0212528 /app/db/migrate/20220525122759_init_schema.rb:168 (lambda)>, :force=>:cascade})
-> 0.0274s
-- create_table("organizations", {:id=>:uuid, :default=>#<Proc:0x00007f17a014c558 /app/db/migrate/20220525122759_init_schema.rb:178 (lambda)>, :force=>:cascade})
-> 0.0256s
-- create_table("plans", {:id=>:uuid, :default=>#<Proc:0x00007f17a0147918 /app/db/migrate/20220525122759_init_schema.rb:188 (lambda)>, :force=>:cascade})
-> 0.0307s
-- create_table("subscriptions", {:id=>:uuid, :default=>#<Proc:0x00007f17a0140938 /app/db/migrate/20220525122759_init_schema.rb:204 (lambda)>, :force=>:cascade})
-> 0.0229s
-- create_table("users", {:id=>:uuid, :default=>#<Proc:0x00007f17a00cbc28 /app/db/migrate/20220525122759_init_schema.rb:219 (lambda)>, :force=>:cascade})
-> 0.0143s
-- add_foreign_key("add_ons", "organizations")
-> 0.0054s
-- add_foreign_key("billable_metrics", "organizations")
-> 0.0043s
-- add_foreign_key("charges", "billable_metrics")
-> 0.0040s
-- add_foreign_key("charges", "plans")
-> 0.0041s
-- add_foreign_key("credits", "applied_coupons")
-> 0.0036s
-- add_foreign_key("credits", "invoices")
-> 0.0041s
-- add_foreign_key("customers", "organizations")
-> 0.0035s
-- add_foreign_key("events", "customers")
-> 0.0034s
-- add_foreign_key("events", "organizations")
-> 0.0039s
-- add_foreign_key("fees", "charges")
-> 0.0034s
-- add_foreign_key("fees", "invoices")
-> 0.0039s
-- add_foreign_key("fees", "subscriptions")
-> 0.0039s
-- add_foreign_key("invoices", "subscriptions")
-> 0.0032s
-- add_foreign_key("memberships", "organizations")
-> 0.0039s
-- add_foreign_key("memberships", "users")
-> 0.0040s
-- add_foreign_key("plans", "organizations")
-> 0.0033s
-- add_foreign_key("subscriptions", "customers")
-> 0.0043s
-- add_foreign_key("subscriptions", "plans")
-> 0.0043s
== 20220525122759 InitSchema: migrated (0.5539s) ==============================
I, [2024-07-05T19:58:50.581536 #131] INFO -- : Migrating to CreateAppliedAddOns (20220526101535)
== 20220526101535 CreateAppliedAddOns: migrating ==============================
-- create_table(:applied_add_ons, {:id=>:uuid})
-> 0.0398s
== 20220526101535 CreateAppliedAddOns: migrated (0.0399s) =====================
I, [2024-07-05T19:58:50.629006 #131] INFO -- : Migrating to AddMetadataToEvents (20220530091046)
== 20220530091046 AddMetadataToEvents: migrating ==============================
-- add_column(:events, :metadata, :jsonb, {:null=>false, :default=>{}})
-> 0.0100s
== 20220530091046 AddMetadataToEvents: migrated (0.0101s) =====================
I, [2024-07-05T19:58:50.646858 #131] INFO -- : Migrating to AddAddOnToFees (20220601150058)
== 20220601150058 AddAddOnToFees: migrating ===================================
-- add_reference(:fees, :applied_add_on, {:type=>:uuid, :foreign_key=>true, :index=>true})
-> 0.0208s
== 20220601150058 AddAddOnToFees: migrated (0.0210s) ==========================
I, [2024-07-05T19:58:50.675777 #131] INFO -- : Migrating to AddBillChargesMonthlyToPlans (20220602145819)
== 20220602145819 AddBillChargesMonthlyToPlans: migrating =====================
-- add_column(:plans, :bill_charges_monthly, :boolean)
-> 0.0072s
== 20220602145819 AddBillChargesMonthlyToPlans: migrated (0.0074s) ============
I, [2024-07-05T19:58:50.691354 #131] INFO -- : Migrating to AddChargesFromDateOnInvoices (20220607082458)
== 20220607082458 AddChargesFromDateOnInvoices: migrating =====================
-- add_column(:invoices, :charges_from_date, :date)
-> 0.0069s
== 20220607082458 AddChargesFromDateOnInvoices: migrated (0.0069s) ============
I, [2024-07-05T19:58:50.705939 #131] INFO -- : Migrating to CreatePaymentProviders (20220609080806)
== 20220609080806 CreatePaymentProviders: migrating ===========================
-- create_table(:payment_providers, {:id=>:uuid})
-> 0.0228s
== 20220609080806 CreatePaymentProviders: migrated (0.0229s) ==================
I, [2024-07-05T19:58:50.736166 #131] INFO -- : Migrating to CreatePaymentProviderCustomers (20220610134535)
== 20220610134535 CreatePaymentProviderCustomers: migrating ===================
-- create_table(:payment_provider_customers, {:id=>:uuid})
-> 0.0383s
== 20220610134535 CreatePaymentProviderCustomers: migrated (0.0384s) ==========
I, [2024-07-05T19:58:50.782597 #131] INFO -- : Migrating to AddPaymentProviderToCustomers (20220610143942)
== 20220610143942 AddPaymentProviderToCustomers: migrating ====================
-- add_column(:customers, :payment_provider, :string)
-> 0.0057s
== 20220610143942 AddPaymentProviderToCustomers: migrated (0.0058s) ===========
I, [2024-07-05T19:58:50.796451 #131] INFO -- : Migrating to AddInvoiceTypeToInvoices (20220613130634)
== 20220613130634 AddInvoiceTypeToInvoices: migrating =========================
-- add_column(:invoices, :invoice_type, :integer, {:null=>false, :default=>0})
-> 0.0081s
== 20220613130634 AddInvoiceTypeToInvoices: migrated (0.0081s) ================
I, [2024-07-05T19:58:50.811793 #131] INFO -- : Migrating to AddStatusToInvoices (20220614110841)
== 20220614110841 AddStatusToInvoices: migrating ==============================
-- add_column(:invoices, :status, :integer, {:null=>false, :default=>0})
-> 0.0107s
== 20220614110841 AddStatusToInvoices: migrated (0.0109s) =====================
I, [2024-07-05T19:58:50.830901 #131] INFO -- : Migrating to CreatePayments (20220617124108)
== 20220617124108 CreatePayments: migrating ===================================
-- create_table(:payments, {:id=>:uuid})
-> 0.0422s
== 20220617124108 CreatePayments: migrated (0.0423s) ==========================
I, [2024-07-05T19:58:50.881349 #131] INFO -- : Migrating to AddInvoiceFieldsToOrganizations (20220620141910)
== 20220620141910 AddInvoiceFieldsToOrganizations: migrating ==================
-- change_table(:organizations, {:bulk=>true})
-> 0.0075s
== 20220620141910 AddInvoiceFieldsToOrganizations: migrated (0.0076s) =========
I, [2024-07-05T19:58:50.896124 #131] INFO -- : Migrating to AddSlugToCustomers (20220620150551)
== 20220620150551 AddSlugToCustomers: migrating ===============================
-- change_table(:customers, {:bulk=>true})
-> 0.0064s
W, [2024-07-05T19:58:51.111606 #131] WARN -- : Scoped order is ignored, it's forced to be batch order.
bin/rails aborted!
StandardError: An error has occurred, this and all later migrations canceled: (StandardError)
PG::DuplicatePstatement: ERROR: prepared statement "a1" already exists
/app/lib/tasks/customers.rake:6:in `block (2 levels) in <main>'
/app/db/migrate/20220620150551_add_slug_to_customers.rb:11:in `change'
Caused by:
ActiveRecord::StatementInvalid: PG::DuplicatePstatement: ERROR: prepared statement "a1" already exists (ActiveRecord::StatementInvalid)
/app/lib/tasks/customers.rake:6:in `block (2 levels) in <main>'
/app/db/migrate/20220620150551_add_slug_to_customers.rb:11:in `change'
Caused by:
PG::DuplicatePstatement: ERROR: prepared statement "a1" already exists (PG::DuplicatePstatement)
/app/lib/tasks/customers.rake:6:in `block (2 levels) in <main>'
/app/db/migrate/20220620150551_add_slug_to_customers.rb:11:in `change'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
root@38c541a217ca:/app/scripts# env | grep -i schema
POSTGRES_SCHEMA=lago_dbHope that would be of help! |
|
I guess I've found the root case and I guess I shall give up with running Lago against external database - modifying |
## Context This PR is a fix for #1945 (comment) ## Description Lago startup script is relying on the `rails db:migrate:primary` to perform the migration at startup. Since in some situation, only one database is configured in the `database.yml` file, the command is failing with the following error: ``` Unrecognized command "db:migrate:primary" (Rails::Command::UnrecognizedCommandError) Did you mean? db:migrate:up ``` This PR fixes this by using `rails db:migrate` instead when only one database is present.
|
Thanks for all the details @alexander-potemkin Good to know about the permissions. We will update the documentation with more details to help future users. Regarding the issue, you can probably try to disable prepared statements by setting |
|
@vincent-pochet , apologies for the delay in getting back to you. non-public schema works as well - thank you! The only problem seems to be somewhere around Redis cache - I've just migrated test environment to another database and I'm getting random errors saying that table doesn't exist. It's resolved with docker restart and when it's not - with redis cache cleanup. At least it seems like so for now. |
|
Shall anyone follow this thread: it seems like using anything except then The solution was to restart the service and immediately everything went back to normal. So, guess it's better stick to something that is well tested - |
|
Thank you @alexander-potemkin for these feedback |
|
Thank you! |
Is your feature request related to a problem? Please describe.
Currently Lago always migrates in the
publicdatabase schema (namespace).Describe the solution you'd like
Allow users to specify a schema with a configuration variable. It would be nice if this had defaulted to
lago.The text was updated successfully, but these errors were encountered: