I’m transferring sites to a new server and have found that phpPgAdmin works on the old server but on the new server it asks for a password. The pgsql databases are empty and have no tables as if the database was created but not imported/migrated.
Ran the transfer with debug, here’s the relevant output but no issues.
DEBUG: syncing pgsql databases...
DEBUG : DEBUG: creating `username_pgsql_tt-rss_02'
DEBUG: creating `username_pgsql_tt-rss_02'
DEBUG : DEBUG: adding backup task for username_pgsql_tt-rss_02
DEBUG: adding backup task for username_pgsql_tt-rss_02
DEBUG : DEBUG: creating `username_pgsql_tt-rss_01'
DEBUG: creating `username_pgsql_tt-rss_01'
DEBUG : DEBUG: adding backup task for username_pgsql_tt-rss_01
DEBUG: adding backup task for username_pgsql_tt-rss_01
DEBUG : DEBUG: syncing postgresql users...
DEBUG: syncing postgresql users...
DEBUG : DEBUG: adding username
DEBUG: adding username
DEBUG : DEBUG: adding username_dbuser_tt-rss
DEBUG: adding username_dbuser_tt-rss
DEBUG : DEBUG: syncing pgsql db `username_pgsql_tt-rss_02'
DEBUG: syncing pgsql db `username_pgsql_tt-rss_02'
DEBUG : DEBUG: LOCAL: pg_dump username_pgsql_tt-rss_02 > /home/virtual/site144/fst/tmp/username_pgsql_tt-rss_02.sql
DEBUG: LOCAL: pg_dump username_pgsql_tt-rss_02 > /home/virtual/site144/fst/tmp/username_pgsql_tt-rss_02.sql
DEBUG : DEBUG: LOCAL: rsync -HW /home/virtual/site144/fst/tmp/username_pgsql_tt-rss_02.sql root@p108.lithium.hosting:/home/virtual/site116/fst/tmp/username_pgsql_tt-rss_02.sql
DEBUG: LOCAL: rsync -HW /home/virtual/site144/fst/tmp/username_pgsql_tt-rss_02.sql root@p108.lithium.hosting:/home/virtual/site116/fst/tmp/username_pgsql_tt-rss_02.sql
DEBUG : DEBUG: REMOTE: find /home/virtual/site116/fst/tmp/username_pgsql_tt-rss_02.sql -noleaf -type f -exec grep -I -m1 -q /home/virtual/site144/fst {} \; -fprint /home/virtual/site116/fst/changed.log -print0 | xargs -0 perl -n -p -i -e 'use re "eval";' -e '$oldsite="/home/virtual/site144/fst"; $newsite="/home/virtual/site116/fst";' -e '$len = length($newsite)-length($oldsite) ; s!s:(\d+)(?{$l=$^N+$len}):"$oldsite!s:$l:"$newsite!g ; s!$oldsite!$newsite!g'
DEBUG: REMOTE: find /home/virtual/site116/fst/tmp/username_pgsql_tt-rss_02.sql -noleaf -type f -exec grep -I -m1 -q /home/virtual/site144/fst {} \; -fprint /home/virtual/site116/fst/changed.log -print0 | xargs -0 perl -n -p -i -e 'use re "eval";' -e '$oldsite="/home/virtual/site144/fst"; $newsite="/home/virtual/site116/fst";' -e '$len = length($newsite)-length($oldsite) ; s!s:(\d+)(?{$l=$^N+$len}):"$oldsite!s:$l:"$newsite!g ; s!$oldsite!$newsite!g'
DEBUG : DEBUG: REMOTE: rm -f /home/virtual/site116/fst/tmp/username_pgsql_tt-rss_02
DEBUG: REMOTE: rm -f /home/virtual/site116/fst/tmp/username_pgsql_tt-rss_02
DEBUG : DEBUG: syncing pgsql db `username_pgsql_tt-rss_01'
DEBUG: syncing pgsql db `username_pgsql_tt-rss_01'
DEBUG : DEBUG: LOCAL: pg_dump username_pgsql_tt-rss_01 > /home/virtual/site144/fst/tmp/username_pgsql_tt-rss_01.sql
DEBUG: LOCAL: pg_dump username_pgsql_tt-rss_01 > /home/virtual/site144/fst/tmp/username_pgsql_tt-rss_01.sql
DEBUG : DEBUG: LOCAL: rsync -HW /home/virtual/site144/fst/tmp/username_pgsql_tt-rss_01.sql root@p108.lithium.hosting:/home/virtual/site116/fst/tmp/username_pgsql_tt-rss_01.sql
DEBUG: LOCAL: rsync -HW /home/virtual/site144/fst/tmp/username_pgsql_tt-rss_01.sql root@p108.lithium.hosting:/home/virtual/site116/fst/tmp/username_pgsql_tt-rss_01.sql
DEBUG : DEBUG: REMOTE: find /home/virtual/site116/fst/tmp/username_pgsql_tt-rss_01.sql -noleaf -type f -exec grep -I -m1 -q /home/virtual/site144/fst {} \; -fprint /home/virtual/site116/fst/changed.log -print0 | xargs -0 perl -n -p -i -e 'use re "eval";' -e '$oldsite="/home/virtual/site144/fst"; $newsite="/home/virtual/site116/fst";' -e '$len = length($newsite)-length($oldsite) ; s!s:(\d+)(?{$l=$^N+$len}):"$oldsite!s:$l:"$newsite!g ; s!$oldsite!$newsite!g'
DEBUG: REMOTE: find /home/virtual/site116/fst/tmp/username_pgsql_tt-rss_01.sql -noleaf -type f -exec grep -I -m1 -q /home/virtual/site144/fst {} \; -fprint /home/virtual/site116/fst/changed.log -print0 | xargs -0 perl -n -p -i -e 'use re "eval";' -e '$oldsite="/home/virtual/site144/fst"; $newsite="/home/virtual/site116/fst";' -e '$len = length($newsite)-length($oldsite) ; s!s:(\d+)(?{$l=$^N+$len}):"$oldsite!s:$l:"$newsite!g ; s!$oldsite!$newsite!g'
DEBUG : DEBUG: REMOTE: rm -f /home/virtual/site116/fst/tmp/username_pgsql_tt-rss_01
DEBUG: REMOTE: rm -f /home/virtual/site116/fst/tmp/username_pgsql_tt-rss_01
Trying to restore a pgsql backup from in ApisCP says success but fails to actually populate the database.
I wouldn’t worry about size unless the database has been recently vacuumed.
pg_dump -x --file=/path/to/file DBNAME is the raw command. How large is this file? Is the account near its quota limit? Import routine is pg_restore -x -O /path/to/file. One of those is acting abnormal.
Edit: do the tables appear within phpPgAdmin on the old server for that user? It may be a schema ownership issue.
The size is an issue because the database is empty, there are no tables.
The tables do appear on the old server and phpPgAdmin works without issue whereas the new server said it needed to have a password set just to launch it.
The account is not near the quota for disk or inodes.
Size differences can arise due to record layout. They can have different sizes on different servers despite having the same record count if a vacuum is pending, so it’s not a reliable metric. Auto vacuum will fail if missing tablespaces are encountered. I had a couple servers like this.
Clearly we are missing tables so I would need you to report back what I asked with pg_dump.
Old/new server may use different password hashes. What version are you coming from/going to?
My point about the file size is that the tables are missing from the new server and 7mb vs 150mb is not a record layout issue.
You want me to run pg_dump -x --file=/path/to/file on the old server to do what exactly? There’s a backups in the pgsql_backups folder if that’s what you’re concerned about.
This is from the new server, but it 100% matches the old server, except the 25th which is backing up an empty database.
~]# ll /home/virtual/domain.com/home/username/pgsql_backups/
total 377208
-rw------- 1 admin116 admin116 63244335 Aug 19 03:34 username_pgsql_tt-rss_01-20240819080820.zip
-rw------- 1 admin116 admin116 63214362 Aug 21 07:41 username_pgsql_tt-rss_01-20240821120806.zip
-rw------- 1 admin116 admin116 63606933 Aug 24 02:29 username_pgsql_tt-rss_01-20240824070813.zip
-rw------- 1 admin116 admin116 471 Aug 25 03:27 username_pgsql_tt-rss_01-20240825080825.zip
-rw------- 1 admin116 admin116 65389554 Aug 19 03:34 username_pgsql_tt-rss_02-20240819080837.zip
-rw------- 1 admin116 admin116 65389554 Aug 21 07:41 username_pgsql_tt-rss_02-20240821120824.zip
-rw------- 1 admin116 admin116 65389554 Aug 24 02:29 username_pgsql_tt-rss_02-20240824070832.zip
-rw------- 1 admin116 admin116 471 Aug 25 03:27 username_pgsql_tt-rss_02-20240825080825.zip
Both servers are on Edge Major, so I doubt that’s the issue. What else can I provide you?
This is the phpphadmin issue post-migration.
Edit:
I deleted the account on the new server, unsuspended the account on the old server.
Performed a fresh migration, it paused on the PGSQL db dumps like it was working but still the databases restored on the new server are “empty” and the phppgadmin page does this thing.
Everything works on the old server, none of the pgsql stuff works on the new. It’s a fresh install of ApisCP on Rocky 8 and is on Edge-Major as is the old.
Again, it depends on UPDATE/DELETE frequency and if autovacuum ever ran against the database. Until a vacuum occurs, these records are not physically removed from the filesystem. apnscp.sessions were north of 1 GB for me and under 10 MB once the missing tablespaces, which prevented autovacuum from occurring, were resolved. Don’t rely on database size alone, always check tables/records were migrated over with PostgreSQL. You can also look at pg_stat_user_tables to determine when autovacuum was last run, e.g.
select * from pg_stat_user_tables where relname = 'sessions'
Verify the database is backing up properly as superuser. mysqldump, for example, will continue a dump if a table is corrupted. Next, dump the database in the panel as the user, the file size should be roughly the same.
If we know pg_export and Export in the control panel are producing nearly identical files, then that’s not a problem.
Next, check ~/.pgpass; this file is read for authentication on both old/new servers. Running psql from within the account’s vfs will use these credentials.
pgsql? I thought you meant ApisCP, not super specific in your request.
Old: 11.22
New: 15.8
The tables are completely missing, I’m not sure if you missed that or not. I’ll try to dump as root and see if it fails.
Edit:
Dump works fine.
root root 265M Aug 26 08:29 test.sql
user user 265M Aug 26 08:30 user_pgsql_tt-rss_02.sql
It exists on the old and the new but the password hash is different in the file.
When on the new server, psql <database> prompts for a password. So it’s not doing something right which may have to do with the databases being empty.
As root, the database has none of the tables or records on the new server.
Edit:
The .pgpass file on the old is 127.0.0.1:*:*::PASSWORD
On the new server it’s 127.0.0.1:*:*:*:md5SOMETHINGELSE
Quite aware. As a rule of thumb for migrating PostgreSQL, don’t rely on database size, a consistency check is the way to do it (foreign keys, table presence, record count, etc).
File sync happens after synchronizing PostgreSQL password in migration, so were steps done on the new server to correct access? If you copy the .pgsql file over from the old server, are you able to use psql within the account’s vfs to access PostgreSQL?
Lastly on the new server, log into Postgres as root,
psql appldb
Then check the hashed password,
SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'username';
What does the rolpassword field look like?
Why are we still arguing about this? You’re focused on the file size, that’s irrelevant if the actual tables are completely missing!!!
As for everything else, no actions were taken on the new server. That .pgsql file was fresh after a stage 0 migration. I then deleted the account and migration dns record to avoid the account migrating until it can be done so properly.
So as it stands, the account does not exist on the new server, every attempt to transfer it has failed with regards to pgsql.
What what does the rolname and rolpassword have to do with anything, so I understand what you’re looking for.
I just had a second customer complain of the same issue, psql database is empty after migration.
They tried to restore the database backup and got an error:
SQL error:
ERROR: permission denied for schema public
LINE 1: CREATE TABLE sigmaris_category (
^
In statement:
CREATE TABLE sigmaris_category (
id integer NOT NULL,
name character varying(191) NOT NULL,
kind smallint DEFAULT 0,
"lastUpdate" bigint DEFAULT 0,
error smallint DEFAULT 0,
attributes text
);
Well, this is two completely different servers, so it seems to just be a transfersite + pgsql issue and not this one old or new server since it’s now two old servers migrating to two new servers.
I did have a similar issue on another old server migration but I think that customer deleted and recreated their database.
This is still an issue, these two accounts are preventing me from wrapping up the migrations and is a blocker for starting new ones knowing psql could break.
It’s a matter in transitioning from PostgreSQL’s older authentication scheme (md5) to SCRAM-SHA-256, that is something I’m unsure why it’s not a 1:1 transition yet. There’s also a change in grants with public schema no longer opt-in by default. Moving from an older release of PostgreSQL where this was unplanned to anything 15+ requires database rework to keep those permissions accessible.
Looking into it, TBD for now.
Thanks for the bug report. Fixed in edge. Both md5 and SCRAM passwords are handled correctly now.
Updated both servers, ran a transfer and phppgadmin doesn’t load on the new server.
It prompts for the password.
Both the old and new server have identical .pgpass now.
The databases are also void of tables still, just empty databases.
OLD SERVER:
[root@p110 ~]# psql username_pgsql_tt-rss_01
psql (11.22)
Type "help" for help.
username_pgsql_tt-rss_01-# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------------+-------+----------
public | ttrss_access_keys | table | username
...
NEW SERVER:
[root@p108 ~]# psql username_pgsql_tt-rss_01
psql (15.8)
Type "help" for help.
username_pgsql_tt-rss_01=# \dt
Did not find any relations.