• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

helping postgresql after OS update

netaccs

Member


Messages: 44

#1
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

Administrator
Staff member
Administrator
Moderator

Thanks: 6,000
Messages: 26,773

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

SirDice

Administrator
Staff member
Administrator
Moderator

Thanks: 6,000
Messages: 26,773

#4
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

Member


Messages: 44

#5
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

Son of Beastie

Thanks: 1,194
Messages: 2,562

#6
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

Administrator
Staff member
Administrator
Moderator

Thanks: 6,000
Messages: 26,773

#7
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.

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

Member


Messages: 44

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

Code:
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

Son of Beastie

Thanks: 1,194
Messages: 2,562

#9
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

Well-Known Member

Thanks: 67
Messages: 319

#10
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

Member


Messages: 44

#11
Edit /var/db/postgres/data96/postgresql.conf
Changing these options:
Code:
data_directory = 'usr/local/pgsql/data'
and got error:
Code:
/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

Well-Known Member

Thanks: 67
Messages: 319

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

xtaz

Well-Known Member

Thanks: 67
Messages: 319

#14
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

Member


Messages: 44

#15
502 creates this files:
Code:
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

Member


Messages: 44

#16
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

Son of Beastie

Thanks: 1,194
Messages: 2,562

#17
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.
 
Top