Tags
PostgreSQL
backup
peertube
  1. ai autopostgresqlbackup
  2. dpkg -L autopostgresqlbackup
  3. mkdir /var/backups/autopostgresqlbackup
  4. vim /etc/default/autopostgresqlbackup
  5. /usr/sbin/autopostgresqlbackup
  6. l /var/backups/autopostgresqlbackup/*/*

PeerTube

  1. https://docs.joinpeertube.org/maintain/migration 
  2. vim $(which autopostgresqlbackup) +361 #367
    • su $SU_USERNAME -l -c "pg_dump -Fc $PGHOST $OPT $db -f $2"
    • pg_dump -Fc --username=$USERNAME $PGHOST $OPT $db -f $2

1. Identify Dump Format

If pg_restore reports:

input file appears to be a text format dump. Please use psql

The dump is plain SQL and must be restored using psql.

Check format (optional):

file peertube_prod_2025-12-05_06h25m.Friday.sql
head -n 5 peertube_prod_2025-12-05_06h25m.Friday.sql

2. Move Dump File Out of /root

The postgres user cannot access /root, even if permissions are changed.

mkdir -p /var/backups/peertube
mv ~/peertube_prod_2025-12-05_06h25m.Friday.sql /var/backups/peertube/
chown postgres:postgres /var/backups/peertube/peertube_prod_2025-12-05_06h25m.Friday.sql

3. Stop PeerTube Before Restore

systemctl stop peertube

4. Drop and Recreate the Database

If the database should be replaced completely:

sudo -u postgres psql -c "DROP DATABASE IF EXISTS peertube_prod;"
sudo -u postgres psql -c "CREATE DATABASE peertube_prod OWNER peertube ENCODING 'UTF8';"

If the peertube user does not exist:

sudo -u postgres psql -c "CREATE USER peertube WITH PASSWORD 'CHANGEME';"
sudo -u postgres psql -c "ALTER DATABASE peertube_prod OWNER TO peertube;"

5. Restore the SQL Dump

Move into the backup directory:

cd /var/backups/peertube

Restore the dump:

sudo -u postgres psql -d peertube_prod -f peertube_prod_2025-12-05_06h25m.Friday.sql

This may take a while depending on database size.

6. Fix Permissions (If Needed)

If PeerTube logs show permission errors:

sudo -u postgres psql -d peertube_prod -c "GRANT ALL PRIVILEGES ON DATABASE peertube_prod TO peertube;"
sudo -u postgres psql -d peertube_prod -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO peertube;"
sudo -u postgres psql -d peertube_prod -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO peertube;"

7. Restart PeerTube Service

systemctl start peertube
systemctl status peertube

If the service fails:

journalctl -u peertube -n 100 -e

Notes

  • Always stop PeerTube before restoring to avoid conflicts.
  • Plain SQL dumps must be restored using psql, not pg_restore.
  • The postgres user cannot read files inside /root; move the dump to another directory.