Restore Deleted Calendars

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