Hello Everybody,
I just set up an xwiki with PostgreSQL on an Ubuntu machine. As I can read in the xwiki documentation, I try to do a backup my xwiki with
pg_dump xwiki > xwiki.sql
but I get an error message:
pg_dump: [archiver (db)] connection to database “xwiki” failed: FATAL: role “vince” does not exist
What am I doing wrong?
Thank you for any help!
All the best,
Vince
Hi,
“vince” postgres account missing (this account must have right access to your postgresql server and xwiki database).
Anyway, you can use “postgres” account to dump your database, or better a dedicated postgres account with enough right access.
You can fill ~/.pgpass with “hostname:port:database:username:password”:
(don(t forget ACL on this file)
You can check this doc too: https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Installation/InstallationWAR/InstallationPostgreSQL/#HManualInstallation
Pascal B
Hi PBas!
thank you for your reply. I tried with “postgres” user like this : pg_dump -U postgres xwiki > xwiki.sql
but I get the following error message: pg_dump: [archiver (db)] connection to database “xwiki” failed: FATAL: Peer authentication failed for user “postgres”.
What do you mean with “a dedicated postgres account with enough right access.” ? Should I create a “postgres” account on my linux machine?
I installed xwiki/postgresql via debian apt, so it created everything by itself for me automatically.
I tried with the ~/.pgpass but it didn’t help…
I’m new to xwiki and postgresql, sorry for the newby questions 
Thank you anyway!
All the best,
Vince
Ok I didn’t know xwiki apt package…
Did you test this way?
Anyway with postgres account I think you can dump all postgresql database (from linux root account in a terminal):
su postgres
pg_dump xwiki > xwiki.sql
I managed to reset the postgres user password with
sudo passwd postgres
now I can login into postgres user, but I still get an error message:
pg_dump xwiki > xwiki.sql
bash: xwiki.sql: Permission denied
Do you have any idea?
all the best
vince
This looks to me more like a file system access problem than a DB access rights issue. The pg_dump process is not allowed to write to the file xwiki.sql, wherever this might be located.
Does pg_dump xwiki > /dev/null work? (Or course this does not place the dump in a location from where it can be restored 
) Or pg_dump xwiki > /tmp/xwiki.sql might be better for testing …
Hey Clemens,
thank you for your reply. Unfortunately I’m still sticking with the same problem.
I installed xwiki on a Linuxmint 18.3 machine, using the deb repositories of xwiki.
When I try pg_dump xwiki > /tmp/xwiki.sql I get:
pg_dump: [archiver (db)] connection to database "xwiki" failed: FATAL: role "lokadmin" does not exist
What is the issue? Is this a permission issue?
All the best and thank you for the help!
Vince
pg_hba has peer auth enabled, the fastest way to resolve this would be to change pg_hba to allow ‘trust’ authentication for localhost, meaning that localhost connections (unix socket) will not be required to auth.
Once you do that pg_dump etc… will just work, but you also want to go into postgresql as the user ‘postgres’ and create a new SUPERUSER account, you can then use this account in the future for logging in; you might want to update your auth method to md5
Hello everybody,
now I achieved to create a backup using !:
sudo -u postgres pg_dumpall > /media/backup/XWIKIBAK/xwiki.sql
is this a good way at all?
Trying to restore the backup, I get an error message (is is very long, here just an excerpt):
psql:xwiki.sql:44257: ERROR: relation "xwvs_end_date" already exists
psql:xwiki.sql:44264: ERROR: relation "xwvs_ip" already exists
psql:xwiki.sql:44271: ERROR: relation "xwvs_name" already exists
psql:xwiki.sql:44278: ERROR: relation "xwvs_page_saves" already exists
psql:xwiki.sql:44285: ERROR: relation "xwvs_page_views" already exists
psql:xwiki.sql:44292: ERROR: relation "xwvs_start_date" already exists
psql:xwiki.sql:44299: ERROR: relation "xwvs_unique_id" already exists
psql:xwiki.sql:44307: ERROR: constraint "fk2780715a3433fd87" for relation "xwikistrings" already exists
psql:xwiki.sql:44315: ERROR: constraint "fk44132acc763934e1" for relation "activitystream_events_targets" already exists
psql:xwiki.sql:44323: ERROR: constraint "fk5a1cd9a1a947aa5" for relation "xwikidoubles" already exists
psql:xwiki.sql:44331: ERROR: constraint "fk6661970f283ee295" for relation "xwikilargestrings" already exists
psql:xwiki.sql:44339: ERROR: constraint "fk7b845b068e772cc" for relation "feeds_aggregatorurlgroups" already exists
psql:xwiki.sql:44347: ERROR: constraint "fk7f8ab31d231efb9b" for relation "xwikiintegers" already exists
psql:xwiki.sql:44355: ERROR: constraint "fkbe0037834855150f" for relation "feeds_feedentrytags" already exists
psql:xwiki.sql:44363: ERROR: constraint "fkbf7104e8cb8c5002" for r
How can I restore my backup properly?
Thank you for help!
All the best,
Vince
Maybe check https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Backup#HBackupyourdatabase and https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Backup#HRestoreyourdata
PS; haven’t followed the thread, just pointing to this doc in case you don’t know it.
The reason you are getting thatis that you are trying to restore data OVER an already existing dataset, you want to use the -C flag for pg_dumpall (which will add the ‘drop’ statements infront of the data dump; or alternatively clear the databases from postgres before proceeding.
Normally in cases that want this type of backup solution I would suggest using ZFS as a backing storage medium for postgresql and using snapshots which is much easier
Hi @Pbas and all, I tried this
sudo -u postgres pg_dump xwiki > xwiki.sql
Seeing the below statement after executing the above command.
could not change directory to “/home/xxxxx”: Permission denied Is it a warning message ?
And I can see the dump file xwiki.sql on my home directory. Is it a complete dump file? or is it incomplete? And the file size 81 MB. Can I consider it as Perfect dump?