PostgreSQL - added equivalent of the MySQL-specific rebuilddb.sh script, for upgrading

2 files changed, 47 insertions, 10 deletions

diff --git a/README b/README
index ec2e2ec4f..07957c09e 100644
--- a/README
+++ b/README
@@ -745,16 +745,19 @@ to the end first before trying them.
    directory to your new directory.
 9. Copy htaccess.sample to .htaccess in the new directory. Change the
    RewriteBase to use the correct path.
-10. Rebuild the database. Go to your Laconica directory and run the
-   rebuilddb.sh script like this:
-
-   ./scripts/rebuilddb.sh rootuser rootpassword database db/laconica.sql
-
-   Here, rootuser and rootpassword are the username and password for a
-   user who can drop and create databases as well as tables; typically
-   that's _not_ the user Laconica runs as.
-11. Use mysql client to log into your database and make sure that the
-    notice, user, profile, subscription etc. tables are non-empty.
+10. Rebuild the database. For MySQL, go to your Laconica directory and
+    run the rebuilddb.sh script like this:
+
+    ./scripts/rebuilddb.sh rootuser rootpassword database db/laconica.sql
+
+    Here, rootuser and rootpassword are the username and password for a
+    user who can drop and create databases as well as tables; typically
+    that's _not_ the user Laconica runs as.
+    For PostgreSQL databases there is an equivalent, rebuilddb_psql.sh,
+    which operates slightly differently. Read the documentation in that
+    script before running it.
+11. Use mysql or psql client to log into your database and make sure that
+    the notice, user, profile, subscription etc. tables are non-empty.
 12. Turn back on the Web server, and check that things still work.
 13. Turn back on XMPP bots and email maildaemon. Note that the XMPP
     bots have changed since version 0.5; see above for details.
diff --git a/scripts/rebuilddb_psql.sh b/scripts/rebuilddb_psql.sh
new file mode 100755
index 000000000..ac169c205
--- /dev/null
+++ b/scripts/rebuilddb_psql.sh
@@ -0,0 +1,34 @@
+#!/bin/bash
+#
+# ******************************* WARNING *********************************
+# Do not run this script until you have read and understood the information
+# below, AND backed up your database. Failure to observe these instructions
+# may result in losing all the data in your database.
+#
+# This script is used to upgrade Laconica's PostgreSQL database to the
+# latest version. It does the following:
+# 
+#  1. Dumps the existing data to /tmp/rebuilddb_psql.sql
+#  2. Clears out the objects (tables, etc) in the database schema
+#  3. Reconstructs the database schema using the latest script
+#  4. Restores the data dumped in step 1
+#
+# You MUST run this script as the 'postgres' user.
+# You MUST be able to write to /tmp/rebuilddb_psql.sql
+# You MUST specify the laconica database user and database name on the
+# command line, e.g. ./rebuilddb_psql.sh myuser mydbname
+#
+
+user=$1
+DB=$2
+
+cd `dirname $0`
+
+pg_dump -a -D --disable-trigger $DB > /tmp/rebuilddb_psql.sql
+psql -c "drop schema public cascade; create schema public;" $DB
+psql -c "grant all privileges on schema public to $user;" $DB
+psql $DB < ../db/laconica_pg.sql
+psql $DB < /tmp/rebuilddb_psql.sql
+for tab in `psql -c '\dts' $DB -tA | cut -d\| -f2`; do
+  psql -c "ALTER TABLE \"$tab\" OWNER TO $user;" $DB
+done