Transfersite not handling pgsql properly.

Bug reports
21

Is the owner of the relations for the database the same as the primary user on the account? There shouldn’t be anything prohibiting transfer now unless it’s perhaps that?

22

In ApisCP, the database owner listed is the primary user on the account, yes.

23

A new issue, after a previous migration from a couple weeks ago, a customer finally reported issues with PSQL.

When I try restoring from a backup, the control panel seems to think it succeeded, but nothing is added to the database. When I try populating it myself using Django migrations, I get the error Unable to create the django_migrations table (permission denied for schema public). When I delete and recreate the database using the control panel I get the same results.

It seems that the user that’s set to the owner of the database (mqpoftmz in my case) doesn’t have permission to do anything in the schema called “public”, even though that schema is owned by pg_database_owner, a role that is supposed to implicitly include the user that owns the database. When I do \conninfo in the psql shell, it says You are connected to database "inse_db" as user "mqpoftmz" on host "127.0.0.1" at port "5432". But when I try a command such as CREATE TABLE test (test int); in the same shell, I get ERROR: permission denied for schema public.

24

PostgreSQL removed public schema permissions in v15. This may be part of the issue but not reproducible on a v11 → v16 server.

Here’s an example migrating from C7/PostgreSQL 11 to Rocky 8/PostgreSQL 16 with the latest commit, #ccbeea4c released today:

# On C7/P11:
AddDomain  -c pgsql,enabled=1 -c dns,enabled=0 -c siteinfo,domain=test.test -c siteinfo,admin_user=testuser
cpcmd -d test.test pgsql:create-database test
# INFO   : created database `test_test'
su test.test

# Within C7 vfs:
psql test_test
CREATE TABLE test (ts TIMESTAMPTZ);
INSERT INTO test VALUES (NOW());

SELECT * FROM test;
# Reports 1 record
exit

# On C7
/usr/local/apnscp/bin/scripts/transfersite.php -s postgres16.server.com --stage=0 test.test

After migration, hop to the Rocky/P16 server:

su test.test
psql test_test

At this point it connects fine, table data is there as well:

# test_test=> select * from test;
              ts
-------------------------------
 2024-09-01 19:50:35.743668-04
(1 row)

test_test=> insert into test values(now());
INSERT 0 1
test_test=> create table bar (tz TIMESTAMPTZ);
CREATE TABLE
test_test=> insert into bar VALUES(now());
INSERT 0 1

Unless there’s something distinct with that account, I’m at a loss for what’s going on at this stage. I’d need to look at both servers to get a better understanding if the test case is failing.

If the test case isn’t failing, you can just resync permissions/databases with:

/usr/local/apnscp/bin/scripts/transfersite.php -s postgres16.server.com --no-create --force --stage=0 --do=sql_databases,pgsql --do=sql_users,pgsql test.test
25

Was I supposed to do something to enable PSQL 16? These are new servers, unless you just defaulted new installs to 16, 15 was the ApisCP Default.

Your steps above aren’t really any different than what I’ve done.

Transferring existing psql databases with data proves unsuccessful and the data is non existent.
Is there a simple upgrade path from 15 → 16 on these new servers to solve this issue?

Or are you proposing that the changes you made today may solve the failed transfers I’ve experienced?

26

There shouldn’t be a material difference from the release notes as search_path was redone in 15. I am only able to reproduce this with 15. I was able to track down the 0 exit code as well in 15; this has been fixed as of this morning.

Looks good on my end but confirmation is necessary.

It’s not something I officially endorse nor support as the upgrade routine is not simple; downtime is unavoidable. See notes.apiscp.com for community-sponsored notes on migrating.

27

Well shit, I think you fixed it. The only issue I’m seeing of the 3 is:

FAILURE: pgsql_import
Util_Process::formatDataCallProc(): psql: psql:/home/virtual/site74/fst/tmp/username_freshrss.sql:37: ERROR:  must be member of role "username_freshrss"

Pgsql_Module::import(): import failed: psql:/home/virtual/site74/fst/tmp/username_freshrss.sql:37: ERROR:  must be member of role "username_freshrss"

I’m unsure the cause, have you seen it before?

28

Send me a copy of the dump. I think your issue is secondary to the public schema matter.

29

See if the latest update fixes it. If not:

psql sigmaris_freshrss
ALTER ROLE sigmaris_freshrss SET ROLE sigmaris;

Then run again, assuming the prefix and primary admin are the same value.

30

Tried and failed, sent a DM on Discord with details.