This article will help if a user has been deleted and all their calendars are lost and need to be restored. Note that this functionality is not built into VPOP3, and requires the use of Python3 to run a script
First you need to install Python 3.8 or later, and the psycopg2 plugin
You can download Python 3 from https://www.python.org/downloads/
Once you have installed that you can run the following to command to install the psycopg2 plugin (to allow Python to access the database
python -m pip install psycopg2
Then, restore your latest VPOP3 backup using the following steps at a command prompt (alter paths & filenames as necessary). When asked for a PostgreSQL password, the default is 'pgsqlpass'
cd \vpop3\pgsql\bin createdb -U postgres -p 5433 --encoding=SQL_ASCII --owner=vpop3 vpop3temp pg_restore -U postgres -p 5433 -v -d vpop3temp --schema=users --schema=calendar dbbackX.dmp
This will make a temporary database called 'vpop3temp' containing ONLY the calendar and user data, which is all that this restore process needs. It should not take up much space because it does not contain message data.
Now, copy paste the below text into a file called 'restorecalendar.py'
import psycopg2 backup_hostname = 'localhost' backup_port = 5433 backup_username = 'vpop3' backup_password = 'vpop3pass' backup_database = 'vpop3temp' restore_user = 'paul' active_hostname = 'localhost' active_port = 5433 active_username = 'vpop3' active_password = 'vpop3pass' active_database = 'vpop3' def restoreEvents(oldConn, oldCalId, newConn, newCalId): curEvents = oldConn.cursor() curEvents.execute('SELECT scopestart, scopeend, created, lastupdate, filename, content, recurrence, objecttype, uid FROM calendar.events WHERE calid=%s', (oldCalId,)) for scopestart, scopeend, created, lastupdate, filename, content, recurrence, objecttype, uid in curEvents.fetchall(): print(f'Found event {filename}') curNew = newConn.cursor() curNew.execute("INSERT INTO calendar.events (calid, scopestart, scopeend, created, lastupdate, filename, content, recurrence, objecttype, uid) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", (newCalId, scopestart, scopeend, created, lastupdate, filename, content, recurrence, objecttype, uid)) newConn.commit() def restoreCalendars(oldConn, oldUserId, newConn, newUserId): print(f'Restore calendar for user {oldUserId} to {newUserId}') curCalendars = oldConn.cursor() curCalendars.execute('SELECT calid, name, lastupdate, internalname FROM calendar.calendars WHERE ownerid=%s', (oldUserId,)) for calid, name, lastupdate, internalname in curCalendars.fetchall(): print(f'Found calendar {name}') curNew = newConn.cursor() curNew.execute("DELETE FROM calendar.calendars WHERE ownerid=%s AND UPPER(name)=UPPER(%s)", (newUserId, name)) curNew.execute("INSERT INTO calendar.calendars (ownerid, name, lastupdate, internalname) VALUES(%s, %s, %s, %s) RETURNING calid", (newUserId, name, lastupdate, internalname)) ret = curNew.fetchone() newCalId = ret[0] print(newCalId) #restoreACLs(oldConn, calid, newConn, newCalId) print(f'Restore calendar {name} for user {oldUserId} to {newUserId}') restoreEvents(oldConn, calid, newConn, newCalId) newConn.commit() oldConn = psycopg2.connect(host=backup_hostname, port=backup_port, user=backup_username, password=backup_password, dbname=backup_database) cur = oldConn.cursor() cur.execute(f'SELECT usernumber, username FROM users.users WHERE username=\'{restore_user}\'') oldUserId = 0 for usernumber, username in cur.fetchall(): print(f'Found Backup user id {usernumber}') oldUserId = usernumber cur.close() newConn = psycopg2.connect(host=active_hostname, port=active_port, user=active_username, password=active_password, dbname=active_database) cur = newConn.cursor() cur.execute("SELECT usernumber, username FROM users.users WHERE username=%s", (restore_user,)) newUserId = 0 for usernumber, username in cur.fetchall(): print(f'Found Current user id {usernumber}') newUserId = usernumber cur.close() if (oldUserId != 0) and (newUserId != 0): restoreCalendars(oldConn, oldUserId, newConn, newUserId) newConn.close() oldConn.close()
You can edit the variables defined start of the file as appropriate to set the database parameters. You will need to edit the 'restore_user' variable to set the user whose calendars you want to restore.
Note that the restore process will delete ALL the calendars owned by the 'restore_user' and replace them with calendars and events from the backup
To run the restore process, run:
python restorecalendar.py