Wiki source code of Database Backup and Restore Commands
Last modified by Eleni Cojocariu on 2026/09/10 20:35
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | The command that dumps and restores an XWiki database is the database's own, and it differs for each of the [[supported databases>>dev:Community.SupportStrategy.DatabaseSupportStrategy]]. The examples below use the default database name ##xwiki##. | ||
| 2 | |||
| 3 | |=Database|=Dump|=Restore | ||
| 4 | |[[MariaDB>>doc:documentation.xs.admin.installation.methods.install-xwiki-war.install-relational-database.configure-mariadb.WebHome]] and [[MySQL>>doc:documentation.xs.admin.installation.methods.install-xwiki-war.install-relational-database.configure-mysql.WebHome]]|((( | ||
| 5 | {{code language="bash"}} | ||
| 6 | mysqldump -u root -p --add-drop-database --databases xwiki > xwiki.sql | ||
| 7 | {{/code}} | ||
| 8 | )))|((( | ||
| 9 | {{code language="bash"}} | ||
| 10 | mysql -u root -p < xwiki.sql | ||
| 11 | {{/code}} | ||
| 12 | ))) | ||
| 13 | |[[PostgreSQL>>doc:documentation.xs.admin.installation.methods.install-xwiki-war.install-relational-database.configure-postgresql-manually.WebHome]]|((( | ||
| 14 | {{code language="bash"}} | ||
| 15 | pg_dump -U xwiki xwiki > xwiki.sql | ||
| 16 | {{/code}} | ||
| 17 | )))|((( | ||
| 18 | {{code language="bash"}} | ||
| 19 | psql -U xwiki -d xwiki -f xwiki.sql | ||
| 20 | {{/code}} | ||
| 21 | ))) | ||
| 22 | |[[Oracle>>doc:documentation.xs.admin.installation.methods.install-xwiki-war.install-relational-database.configure-oracle-manually.WebHome]]|((( | ||
| 23 | {{code language="bash"}} | ||
| 24 | expdp xwiki@ORCL schemas=xwiki directory=DATA_PUMP_DIR dumpfile=xwiki.dmp | ||
| 25 | {{/code}} | ||
| 26 | )))|((( | ||
| 27 | {{code language="bash"}} | ||
| 28 | impdp xwiki@ORCL schemas=xwiki directory=DATA_PUMP_DIR dumpfile=xwiki.dmp | ||
| 29 | {{/code}} | ||
| 30 | ))) | ||
| 31 | |[[HSQLDB>>doc:documentation.xs.admin.installation.methods.install-xwiki-war.install-relational-database.configure-hsqldb.WebHome]]|((( | ||
| 32 | No command: the database is the ##database## [[sub-directory>>doc:documentation.xs.admin.configuration.directories.permanent-directory.sub-directories.WebHome]] of the permanent directory, and is copied with it. | ||
| 33 | )))|((( | ||
| 34 | No command, for the same reason. | ||
| 35 | ))) | ||
| 36 | |||
| 37 | == Why the MariaDB and MySQL dump drops the database == | ||
| 38 | |||
| 39 | {{warning}} | ||
| 40 | Dump with the option that adds the statement dropping the database, as the table above does. A restore has to remove the tables that exist in the target and not in the dump, because a data migration may have created some. Left in place, they make the migration fail the next time it runs, and the database cannot be brought back to the state the dump holds. | ||
| 41 | {{/warning}} | ||
| 42 | |||
| 43 | The same option makes the dump recreate the database with the character set and collation XWiki needs, so the restore needs no ##CREATE DATABASE## of its own, which is why the restore command above names no database. | ||
| 44 | |||
| 45 | == Dumping every wiki of a farm == | ||
| 46 | |||
| 47 | On MariaDB and MySQL each [[wiki>>doc:documentation.xs.user.base.wikis.WebHome]] of a farm is a database of its own, so dumping ##xwiki## backs up the main wiki alone. Dump them all instead: | ||
| 48 | |||
| 49 | {{code language="bash"}} | ||
| 50 | mysqldump -u root -p --add-drop-database --all-databases > xwiki.sql | ||
| 51 | {{/code}} | ||
| 52 | |||
| 53 | On PostgreSQL every wiki is a [[schema>>doc:documentation.xs.admin.installation.methods.install-xwiki-war.install-relational-database.configure-postgresql-manually.support-multiple-wikis.WebHome]] of the single ##xwiki## database, so the command in the table already covers the whole farm. Use ##pg_dumpall## when you want the server's roles as well, and restore it with ##psql -d postgres -f xwiki.sql##. | ||
| 54 | |||
| 55 | == The database user after a restore == | ||
| 56 | |||
| 57 | Restoring onto a new database server leaves the instance without the user ##hibernate.cfg.xml## connects as. Recreate it with the privileges the [[database installation>>doc:documentation.xs.admin.installation.methods.install-xwiki-war.install-relational-database.WebHome]] gives it, and set its password to the one that file holds: | ||
| 58 | |||
| 59 | {{code language="bash"}} | ||
| 60 | pass=$(sed -n 's|.*<property name="hibernate.connection.password">\(.*\)</property>.*|\1|p' \ | ||
| 61 | /etc/xwiki/hibernate.cfg.xml) | ||
| 62 | mysql -u root -p -e "ALTER USER 'xwiki'@'localhost' IDENTIFIED BY '$pass';" | ||
| 63 | {{/code}} | ||
| 64 | |||
| 65 | == Running the commands from a graphical client == | ||
| 66 | |||
| 67 | [[pgAdmin>>https://www.pgadmin.org/]] performs both operations on a selected PostgreSQL database, as "Backup" and "Restore" in its context menu. |
