Migration Issue Around De-duping Poll Votes

Contribute Bug
7

@sweetbeems here’s a preliminary set of commands which should help restore your data, and I’ve tested out on my site.

I am making an assumption that you are using our docker install and are somewhat familiar with the command line.

You should identify a backup file from <site>/admin/backups. It should be one that was done before the data was deleted.

This guide assumes your backups include uploads. The difference is that the backup file would be .tar.gz (data+uploads) vs .sql.gz (data only)

# enter the app using the default user
docker exec -it app bash

# identify the backup you want is there
ls /var/www/discourse/public/backups/default/

# extract the dump.sql file from compressed backup
cd /var/www/discourse/public/backups
mkdir site-backup-to-restore
tar xfz ./default/=SITE_BACKUP_FILENAME= -C site-backup-to-restore/
cd site-backup-to-restore
gunzip dump.sql.gz

# get the data
# this finds the start of the poll votes data
# to the end of the data (which would be an empty line)
start=$(grep -n "COPY public.poll_votes (poll_id, poll_option_id, user_id, created_at, updated_at) FROM stdin;" dump.sql | sed  's/\([0-9]*\).*/\1/')
end=$(tail -n +$start dump.sql | grep -m 1 -n ^$ | sed  's/\([0-9]*\).*/\1/')
end=$((end-2))
tail -n +$start dump.sql | head -n $end > poll_votes_dump.sql

# here you would want to eyeball poll_votes_dump.sql
cat poll_votes_dump.sql

# change the COPY to an INSERT so that
# we can still add ON CONFLICT
sed -i 's/[0-9]\{4\}-[0-9]\{2\}-[0-9]\{2\} [0-9]\{2\}:[0-9]\{2\}:[0-9]\{2\}\.[0-9]\{6\}/'\''&'\''/g' poll_votes_dump.sql
sed -i '2,$s/\t/,/g' poll_votes_dump.sql
sed -i '2,$s/^/(/; 2,$s/$/),/' poll_votes_dump.sql
sed -i '$ s/,$//' poll_votes_dump.sql
sed -i 's/COPY public.poll_votes (poll_id, poll_option_id, user_id, created_at, updated_at) FROM stdin;/INSERT INTO public.poll_votes (poll_id, poll_option_id, user_id, created_at, updated_at)\nVALUES/' poll_votes_dump.sql
echo "ON CONFLICT DO NOTHING;" >> poll_votes_dump.sql
cat poll_votes_dump.sql
These are what the results of the `cat` commands should look like

The first one should look like this

COPY public.poll_votes (poll_id, poll_option_id, user_id, created_at, updated_at) FROM stdin;
1       1       1       2023-07-04 05:49:17.031031      2023-07-04 05:49:17.031031
1       4       1       2023-07-04 05:54:01.281801      2023-07-04 05:54:01.281801

and the second one should look like this

INSERT INTO public.poll_votes (poll_id, poll_option_id, user_id, created_at, updated_at)
VALUES
(1,1,1,'2023-07-04 05:49:17.031031','2023-07-04 05:49:17.031031'),
(1,4,1,'2023-07-04 05:54:01.281801','2023-07-04 05:54:01.281801')
ON CONFLICT DO NOTHING;

Exit out of the docker instance and into the postgres user – There is a deeper problem which I won’t be addressing here which requires this additional hoop jumping

# from your host, access the app via the postgres user
docker exec --user postgres -it app bash

# insert
psql -U postgres -d discourse -f /var/www/discourse/public/backups/site-backup-to-restore/poll_votes_dump.sql

# you should see `INSERT 0 3928` or the number of poll votes that were restored
4 Likes