# helping postgresql after OS update



## netaccs (Mar 8, 2018)

Hello to all.
Freebsd 9.1 Stable. Upgrade it to 9.3Release, then 10.1Release, then 10.3Release, then 11.1p7 Release. After that upgrade all packages by "pkg upgrade -f"
Update apache22 to 24 and php54 to 72. I have installed postgresql 9.2 and upgrade it to 9.6.

Somehow, may be my fault but now I don't have databases, so webserver can't connect. 
Where did the old database gone, could that upgrading 9.2 to 9.6 init new database ?
In  "/var/db/postgres/data96/base/" I see this folders 1/ 12757/ 12758/ with the same date.

I use 502.pgsql script from periodic  for making backups.
I see that script creates this files -> inv_dump_date.out, inv_dump.out_date.gz, pg_dump_inv_date, pg_dump_postgres_date, pg_dump_template1_date, pgglobals_date.gz

How can I get back my database? 

Thanks for helping !


----------



## SirDice (Mar 8, 2018)

The old database files are in /var/db/postgres/data92/, the "92" and "96" refer to the PostgreSQL version.


----------



## netaccs (Mar 8, 2018)

I have only /data96 in  /var/db/postgres/


----------



## SirDice (Mar 8, 2018)

Well, you either removed it yourself or you weren't running PostgreSQL 9.2. The installation/upgrade of PostgreSQL doesn't remove the old data directory.


----------



## netaccs (Mar 8, 2018)

I don't run "pkg delete", only  "pkg upgrade -f", also manually didn't do anything about postgresql. Apache and php yes, but nothing with the sql.
Anyway... can you help me with restoring the database ?


----------



## ShelLuser (Mar 8, 2018)

On that subject why didn't you simply make a dump using pg_dumpall(1)? It's extremely easy: `pg_dumpall -f mybackup.sql`, and it would also have ensured the safety of your data.

Note though that your databases are normally located in /usr/local/pgsql/data, I'd look there to start with.

(edit)

These do not get reset or anything during updates. So unless you did something weird yourself then your data should still be there.


----------



## SirDice (Mar 8, 2018)

netaccs said:


> I don't run "pkg delete", only "pkg upgrade -f", also manually didn't do anything about postgresql.


Even if you did use pkg-delete(8) the data directory would still be there. Ports/packages never remove or overwrite modified configuration or data files. 



netaccs said:


> Anyway... can you help me with restoring the database ?


I can do quite a lot but I can't magically recreate something that isn't there anymore. 

In any case, you did make backups. They recently failed but you should still have some of the old backups? Start by restoring the last successful backup of the database.


----------



## netaccs (Mar 8, 2018)

Does pg_dumpall making archive of everything - db, roles, templates ?


```
less /usr/local/pgsql/data/PG_VERSION  -> 9.0
```
In  /usr/local/pgsql/data/base/ I see these folders, but they look old, my last backup from 502 is from Mar 06.
drwx------  2 pgsql  pgsql  6144 Apr 16  2017 1/
drwx------  2 pgsql  pgsql  4096 May 12  2014 11818/
drwx------  2 pgsql  pgsql  4096 Apr 16  2017 11826/
drwx------  2 pgsql  pgsql  5120 Apr 16  2017 16941/


----------



## ShelLuser (Mar 8, 2018)

If you have /usr/local/pgsql/data then why bother with any of this? Because that would be your current database. There are no backups there, that's the actual data which PostgreSQL is using. Also see /usr/local/pgsql/data in which you'll find postgresql.conf and pg_hba.conf.

So I somewhat doubt that you need to restore anything.


----------



## xtaz (Mar 8, 2018)

With postgresql if you upgrade it across a major version then you either need to dump/restore the database or you need to use pg_upgrade. Otherwise it will probably refuse to use the old data directory.

The problem with pg_upgrade though is that it requires both the old and new versions installed at the same time which can't happen with the older versions of the packages on FreeBSD as they clash with each other.

If I were you I would carefully read these links below, and maybe reinstall the 9.2 version. Get it working, take a pg_dumpall backup. Upgrade to 9.6. And then restore the backup.

https://www.postgresql.org/docs/9.6/static/release-9-6.html

And I quote "A dump/restore using pg_dumpall, or use of pg_upgrade, is required for those    wishing to migrate data from any previous release."

https://www.postgresql.org/docs/9.6/static/backup-dump.html


----------



## netaccs (Mar 8, 2018)

Edit /var/db/postgres/data96/postgresql.conf 
Changing these options:

```
data_directory = 'usr/local/pgsql/data'
```
and got error:

```
/usr/local/etc/rc.d/postgresql start
LOG:  skipping missing configuration file "/usr/local/pgsql/data/postgresql.auto.conf"
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.0, which is not compatible with this version 9.6.8.
pg_ctl: could not start server
Examine the log output.
```


----------



## xtaz (Mar 8, 2018)

Which is exactly what I said above...... Although it looks like you actually need 9.0 and not 9.2.


----------



## netaccs (Mar 8, 2018)

How can I installed, it is missing from ports? 
Can I use somehow backups that  I have, why this 502 scripts makes them?


----------



## xtaz (Mar 9, 2018)

I don't personally use the 502 periodic script so I don't know exactly what it does. But having a quick look at it would suggest that it runs pg_dumpall or pg_dump. So you should be able to use the contents of these files to load into the newer version of postgresql using the same instructions as in the backup-dump.html page I linked to above.

Per database backups look like they are named pgdump_${db}_${now} so looking at what you posted originally I guess your database is called inv ? So you can probably do something like psql < pg_dump_inv_date


----------



## netaccs (Mar 12, 2018)

502 creates this files:

```
pgdump_inv_2018-03-05
pgdump_template1_2018-03-05
inv_dump.out-2018-03-05.gz -> empty
pgglobals_2018-03-05.gz
inv_dump-2018-03-05.out
```
When trying to import database there were message about incompatible. I convert dump in to sql using `pg_restore db.dump > db.sql`
Wsd  I will try on one computer, which has 9.0 and 9.4 in ports.

I found this guide. Should I use this method with jails or there is another way to run pg_upgrade or may be this one?


----------



## netaccs (Mar 13, 2018)

I restore the database and everything works ok. Learn a lesson about upgrading.

I continue to use 502.pgsql script for backups.
Also using this line /usr/local/bin/pg_dump -U inv inv > /data/dbbackup/inv-`date +\%Y-\%m-\%d`.sql
What is the difference running  pg_dumpall -f /data/dbbackup/inv-`date +\%Y-\%m-\%d`.sql with postgres user 
and pg_dump which I am using ?


----------



## ShelLuser (Mar 13, 2018)

Check their respective manual pages and you'll find out. One program is used to dump all databases whereas the other is meant to only dump one or some of them. The reason to use postgres is probably because that account has full access to the databases. PostgreSQL can use a different authentication scheme than that of the underlying OS.


----------

