Restored PostgreSQL .sql Backup – Database Tables Exist but Alfresco Share Shows No Data

[billybill07]

Hello everyone,

I recently performed a backup and restore operation for an Alfresco Community instance and ran into an issue I can’t resolve. Here is what I did step-by-step:

---

✅ Backup Process:

On the source server (Alfresco + PostgreSQL), I created a SQL dump using:

pg_dump -U alfresco -d alfresco > alfresco_backup.sql

✅ Transfer to VM:

I copied the backup file to a clean Linux VM using:

scp alfresco_backup.sql user@vm-ip:/home/user/

✅ Restore Process:

Inside the VM, I created a new PostgreSQL database, then restored the dump using:

psql -U alfresco -d alfresco < /home/user/alfresco_backup.sql

Note: I used the same database name and user credentials as the original instance.

✅ Verification:

After the restore, I verified that the database contains the expected tables and data using:

docker exec -it <your-postgres-container> psql -U alfresco -d alfresco

Inside the PostgreSQL shell:

SELECT COUNT(*) FROM alf_node;

SELECT COUNT(*) FROM alf_authority;

SELECT COUNT(*) FROM alf_store;

These queries returned valid counts, indicating that the node metadata and authorities were restored.

❌ Problem:

Even though the database seems to contain all the data (e.g., alf_node has thousands of rows), Alfresco Share shows no  folders, or users or aspects after starting the containers.

[LeoMattioli]

In the logs there's a different node related to alfrescoUserStore and user Admin

user://alfrescoUserStore/aef78827-cc54-4e2eb788-27cc54fe2ead

And I see at the beginning the bootstrap says there are some Schema validation problems, and then creates some table as the DB is empty

Bit messy: are your starting Alfresco during DB Restore?

[billybill07]

To clarify: Alfresco was not running during the database restore. We made sure to stop all containers before running the pg_restore command, and only restarted the Alfresco stack once the restoration completed successfully.

The message about schema validation and table creation is indeed concerning — it suggests that Alfresco may have initialized a fresh schema before the actual data was available. However, we double-checked the steps and timing, and the restore process should have been clean.

Also, regarding the node user://alfrescoUserStore/aef78827-cc54-4e2eb788-27cc54fe2ead, I’ll look into that further to see if there's a mismatch or missing reference after the restore.

Let me know if you'd recommend a different restore sequence, or if you suspect the issue could be related to PostgreSQL configs, Docker volumes, or another aspect of the environment.

[LeoMattioli]

Hi, my next guess is Docker. Are you using compose or cmd line? Can you share it?

[billybill07]

Thanks for the follow-up.

Yes, I'm using Docker Compose to manage the Alfresco stack. Below is the basic structure of my docker-compose.yml file and the steps I follow:

 Compose Details:

I'm using the official Alfresco Community Docker Compose setup (from Alfresco GitHub) with some minor adjustments, mainly for volume paths and ports.

The main containers involved:

• alfresco/alfresco-content-repository-community:23.3.3

• alfresco/alfresco-search-services:2.0.11

• postgres:16.9 (not containerized — installed directly in the VM)

  Restore Procedure:

  1. Stopped Alfresco containers (using docker-compose down, except for Postgres which is outside Docker).

  2. Ran pg_restore using this command:

     pg_restore -U alfresco -d alfresco --clean --no-owner /path/to/backup.dump

     3. Once the restore was complete, I ran docker-compose up -d to start Alfresco again.

[LeoMattioli]

It's a quite old compose, you can find the latest one here. Remove/comment out the Postgresql section, set the db.url to point to your locally installed DB. I have no experience with pg_restore, so I don't know if the command is correct, however restoring the DB before starting Alfresco is the correct sequence.

In any case, the nodes that are showed as not existent and the Schema validation messages are strange: seems that another DB is stepping in. Another silly question: do you have any other Postgresql DB reachable from the Alfresco machine? Or I don't know if on the same Postgresql server you can have 2 different DB users that can create a DB with the same name.

[billybill07]

There’s only one PostgreSQL instance on the VM, hosting a single database named alfresco, accessed solely by the alfresco user.
I’ve verified this via psql -l, \du, and also checked the data directory — no duplicate DBs or unexpected roles exist.

For the restore, Alfresco services were fully shut down (except PostgreSQL) during the process. I only brought them up via Docker Compose after the restore completed.