Restore postgresql?

Support Installation
1

I made a backup of my yunohost installation few days ago… which packet could i try to restore postgresql ?
Thanks

2

pgadmin

3

Thanks but I don´t see this paket “pgadmin” e.g. under

/yunohost/admin/#/backup/local/create

where can i find it?

4

Each app backup its own postgresql (or mysql) database.

So if you have created a postgres db by your hand, and don’t create a backup hook to tell this db should be backuped (or backuped it by hand), i think you have lost your custom db.

We need more context to help you efficiently.

5

Thanks @ljf ,
a update for synapse did not work on my server


and now synapse does not start anymore…

i have a backup of synapse made with the yunohost webinterface backuptool.
So i could try to restor this.
Do you think this is a good idea?

6

Before to do that, you should try to check if the database is still here on your current system:

sudo -u postgres psql -c "\l"

If no, check if the database dump is inside your backup archive. I mean it’s possible your synapse backup fails due to a problem with postgres… SO you should check your backup contains the dump before to restore it. The dump is called dump.sql. You can open the archive or list all file in it, and check.

tar -tvf  /home/yunohost.backup/archives/ARCHIVE.tar.gz | grep sql
7

But first let’s ask the more obvious question …

Yes, so why not try to restore the backup using the regular procedure to restore yunohost backups … c.f. Restoring backups in YunoHost • index

8

Cause if the dump is not in the backup (the log indicates postgresql failed), this action could trigger the delete of db…

9 
root@my:~# sudo -u postgres psql -c "\l"
could not change directory to "/root": Permission denied
psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

under
/home/yunohost.backup/archives/ i fount the backup the system made before I tried the update. … the archive contains a dump.sql file.
Is it a good idea to try a restore by

$ yunohost backup restore synapse-pre-upgrade2 --debug

???

10

I guess you could first check that postgres is running with pg_lsclusters, but it would really help if first you provided more context about what you’re trying to do and why you’re concerned about postgresql running or not, or why you were looking for a “packet to try to restore postgresql”, instead of simply restoring the backup …

11

thanks @Aleks
a update for synapse did not work on my server

and nowsynapse does not start anymore

i can start postgresql but pg_lsclusters says it is down

root@my:~# service postgresql status

postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Wed 2020-12-02 09:03:54 UTC; 2 weeks 2 days ago
Main PID: 1283 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 4499)
Memory: 0B
CGroup: /system.slice/postgresql.service

Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable.
root@my:~#

root@my:~# pg_lsclusters
    Ver Cluster Port Status Owner    Data directory              Log file
    11  main    5432 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
    root@my:~# service postgresql start
    root@my:~# pg_lsclusters
    Ver Cluster Port Status Owner    Data directory              Log file
    11  main    5432 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
    root@my:~#

the log
/var/log/postgresql/postgresql-11-main.log
file is empty.

what could i try?

12

I have few backups of my sysnapse installation which i want to use because synapse does not work anymore after an update.
I tried but none of the backups could be restored.

Than I reinstalled postgresql by

apt-get --purge remove postgresql\*

and

sudo apt install postgresql

and tried a fresh installation for synapse which worked right away…
I deinstalled synapse and tried to restore one of the backups again – but that sill does not work…

https://paste.yunohost.org/raw/kozehuvako

I really don’t understand why
can someone help - Thanks

13

It seems this line doesn’t work:

chmod 770 /opt/yunohost/matrix-synapse/Coturn_config_rotate.sh

Your postgresql db seems restored.

ping @Josue ?

14

@Josue seams busy

@ljf What could I try? Can I set chmod 770 by hand?

Thanks for your help

15

What could I try?

16

@ljf @Aleks
why can´t i restore the postgresql backups which i made during the time when the system was runing just fine?
Thanks for your help

17

Because Yunohost’s backup is supposed to cover the whole issue in a much more simple way.

So yes, Josue is busy, but fixing that issue about '/opt/yunohost/matrix-synapse/Coturn_config_rotate.sh': No such file or directory sounds simpler than manually restoring a db which may leave other things in an inconsistent state

But if you really are convinced that restoring the DB manually is the way to go : go on and see for yourself.

  • The DB is stored inside the archive in apps/synapse/backup/dump.sql
  • The psql root password is stored on your system in /etc/yunohost/psql
  • You can run something like sudo --login --user=postgres PGUSER=postgres PGPASSWORD=[the psql password] psql matrix_synapse < dump.sql
  • And voila!
18

thanks @Aleks
I´m not convinced, i just want to get sysnapse running again by not losing all my old chats… the server is now down for weeks :frowning:

So i have to fist open the archive and copy apps/synapse/backup/dump.sql to a place on the server - right? Where should i copy dump.sql to?

19

You can copy it anywhere you want, it’s just a ~temporary file that you will inject in the psql command …

Alternatively, if you want to try to fix the restore script “on the fly” (instead of the manual restore of the db) we can try the following :

  • make a copy of your backup archive (literally a backup of the backup…)
  • using some program, edit the restore script located inside the archive … not 100% where it is, i would suspect something like apps/synapse/backup/scripts/restore ?
  • inside the file, find the lines corresponding looking like these : https://github.com/YunoHost-Apps/synapse_ynh/blob/testing/scripts/restore#L239-L240
  • add || true at the end such that it looks like :
chmod 770 $final_path/Coturn_config_rotate.sh || true
chmod 700 $final_path/update_synapse_for_appservice.sh || true
  • try to restore the modified archive
20

Thanks @Aleks
I edited the restore file with a txt editor - like you said…
but some how it did not work

https://paste.yunohost.org/raw/alafiqisak

any idea what i could try next?