Restore problem

[Andyd358]

Hi

I have been testing the back up and restore to a different server I ran it manually last night and it all worked fine from what i could see. I places the script into cron.daily but i think i did something wrong. This morning I tried to connect to the new server and got this:
error: SQLSTATE[08006] [7] could not connect to server: Connection refused Is the server running on host "localhost" :1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

I then tried to manually run the script again but got this error :
psql: could not connect to server: Connection refused
Is the server running on host "127.0.0.1" and accepting
TCP/IP connections on port 5432?

I have not alteres anything in the
/etc/postgresql/9.4/main/postgresql.conf

Any ideas ?

Cheers Andy

 

[Adrian Fretwell]

As the error suggests, check to see if Postresql is actually running. The command ps ax | grep postgres should give you a result similar to what is shown below:

root@a2estest1:~# ps ax | grep postgres
   604 ?        Ss     1:00 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf
   605 ?        S      4:10 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
   726 ?        Ss     0:02 postgres: 11/main: checkpointer  
   727 ?        Ss     0:49 postgres: 11/main: background writer  
   728 ?        Ss     0:56 postgres: 11/main: walwriter  
   729 ?        Ss     1:39 postgres: 11/main: autovacuum launcher  
   730 ?        Ss     2:00 postgres: 11/main: stats collector  
   731 ?        Ss     0:02 postgres: 11/main: logical replication launcher  
   760 ?        Ss     0:01 postgres: 12/main: checkpointer  
   761 ?        Ss     0:48 postgres: 12/main: background writer  
   762 ?        Ss     0:52 postgres: 12/main: walwriter  
   763 ?        Ss     0:31 postgres: 12/main: autovacuum launcher  
   764 ?        Ss     0:30 postgres: 12/main: stats collector  
   765 ?        Ss     0:02 postgres: 12/main: logical replication launcher  
108667 ?        Ss     0:00 postgres: 11/main: fusionpbx fusionpbx 127.0.0.1(37732) idle
125597 pts/0    S+     0:00 grep postgres

Also check for any error messages in the log files in /var/log/postgresql/

 

[Andyd358]

Morning Adrian Hope your well and thanks for replying so quickly

root@pbx:~# ps ax | grep postgres
1850 pts/2 S+ 0:00 grep postgres
root@pbx:~#

 

[Andyd358]

2020-06-21 14:14:20 UTC [24851-1] fusionpbx@fusionpbx ERROR: duplicate key valu
e violates unique constraint "v_xml_cdr_pkey"
2020-06-21 14:14:20 UTC [24851-2] fusionpbx@fusionpbx DETAIL: Key (xml_cdr_uuid
)=(58bb99a4-bd2f-4867-a897-6ec2d41815d8) already exists.

 

[Andyd358]

at the end of the log file i get this
2020-06-25 08:06:01 UTC [25848-2] fusionpbx@fusionpbx STATEMENT: select * from v_domains
2020-06-25 08:06:01 UTC [25848-3] fusionpbx@fusionpbx ERROR: relation "v_software" does
not exist at character 15
2020-06-25 08:06:01 UTC [25848-4] fusionpbx@fusionpbx STATEMENT: select * from v_softwar
e
2020-06-25 08:06:01 UTC [25848-5] fusionpbx@fusionpbx ERROR: relation "v_default_setting
s" does not exist at character 15
2020-06-25 08:06:01 UTC [25848-6] fusionpbx@fusionpbx STATEMENT: select * from v_default
_settings order by default_setting_order asc
2020-06-25 08:06:01 UTC [25848-7] fusionpbx@fusionpbx ERROR: relation "v_default_setting
s" does not exist at character 15
2020-06-25 08:06:01 UTC [25848-8] fusionpbx@fusionpbx STATEMENT: select * from v_default
_settings
2020-06-25 08:06:22 UTC [188-2] LOG: received fast shutdown request
2020-06-25 08:06:22 UTC [188-3] LOG: aborting any active transactions
2020-06-25 08:06:22 UTC [199-2] LOG: autovacuum launcher shutting down
2020-06-25 08:06:22 UTC [195-769] LOG: shutting down
2020-06-25 08:06:22 UTC [195-770] LOG: database system is shut down

 

[Adrian Fretwell]

It sounds like whatever you ran in cron.daily has done something nasty to your database. But at least you have a backup right?

Try to start postgres but make sure nothing is going to try to talk to it, so stop freeswitch and nginx first.

service freeswitch stop
service nginx stop

Now try to start postgres, issue a stop first just to make sure the old service flags and PID files are removed:

service postgresql stop
service postgresql start

If you don't see any error reported by the command then postgres should have started.

su to the postres user:

root@a2estest1:~# su postgres

Now try to connect to the fusion database:

postgres@a2estest1:/var/log/postgresql$ psql fusionpbx
psql (12.2 (Debian 12.2-1.pgdg90+1), server 11.7 (Debian 11.7-1.pgdg90+1))
Type "help" for help.

fusionpbx=#

Once connected to the DB the \dt command should list all the tables.

Follow these steps before you try to repair anything.

 

[Andyd358]

It sounds like whatever you ran in cron.daily has done something nasty to your database. But at least you have a backup right?

This i sthe test server i setup to see if i can do a restore to be honest so im not that fussed but i do need for my own sanity to find out where i have gone wrong. Its a container on proxmmox which i copied from the original and trstored to a different host. Ill try your suggestions before i kill it completely and restore the Container again.

 

[Andyd358]

mmmmm
root@pbx:/etc/cron.daily# service freeswitch stop
root@pbx:/etc/cron.daily# service nginx stop
root@pbx:/etc/cron.daily# service postgresql stop
root@pbx:/etc/cron.daily# service postgresql start
root@pbx:/etc/cron.daily# su postgres
bash: /etc/bash.bashrc: Permission denied
bash-4.3$

 

[Andyd358]

postgres@pbx:~$ psql
psql (9.4.15)
Type "help" for help.

postgres=# \d
No relations found.
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)

postgres=#


so seems the db is no longer there or am i thinking about this incorretly

 

[Adrian Fretwell]

You are correct the fusionpbx DB is no longer there. You should be able to restore it. This is a simple scrip that I use, you will need to edit the backup directory and the password. On a new install of fusionpbx a random password is generated for postgresql, you can find it in /etc/fusionpbx/config.php

#!/bin/bash

BACKUPDIR=/home/hbk/a2estest1
database_host=127.0.0.1
database_port=5432
export PGPASSWORD="xxxxxxxxxxxxxxxxxxxx"


#remove the old database
psql --host=$database_host --port=$database_port  --username=fusionpbx -c 'drop schema public cascade;'
psql --host=$database_host --port=$database_port  --username=fusionpbx -c 'create schema public;'

#restore the database
pg_restore -v -Fc --host=$database_host --port=$database_port --dbname=fusionpbx --username=fusionpbx $BACKUPDIR/database/fusionpbx_pgsql_backup.sql

exit

 

[Andyd358]

You are correct the fusionpbx DB is no longer there. You should be able to restore it. This is a simple scrip that I use, you will need to edit the backup directory and the password. On a new install of fusionpbx a random password is generated for postgresql, you can find it in /etc/fusionpbx/config.php

#!/bin/bash

BACKUPDIR=/home/hbk/a2estest1
database_host=127.0.0.1
database_port=5432
export PGPASSWORD="xxxxxxxxxxxxxxxxxxxx"


#remove the old database
psql --host=$database_host --port=$database_port  --username=fusionpbx -c 'drop schema public cascade;'
psql --host=$database_host --port=$database_port  --username=fusionpbx -c 'create schema public;'

#restore the database
pg_restore -v -Fc --host=$database_host --port=$database_port --dbname=fusionpbx --username=fusionpbx $BACKUPDIR/database/fusionpbx_pgsql_backup.sql

exit

I ended up reinstallin the server although still having issues ill keep you updated