Issues upgrading using Postgres

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Issues upgrading using Postgres

Bugzilla - Users mailing list
Hi All,

I am attempting to upgrade my Bugzilla installation to 5.0.6, using Postgres
9.2 as my database, on Centos7. My old install is on Centos6, using Postgres
8.4. All seems to go well until I import the dump file on the new server. I
have tried copying the data directory and localconfig from the old to the
new server, then running the pg_dump to import the dump, but it just doesn't
seem to be working as advertised. Can someone give me a step by step on how
I should be approaching this? I feel like I've tried every method I can
think of but keep getting the same result, which is nothing.

Steve Linn
MDDC- Sr. Linux Administrator
Desk: (256)450-0384



_______________________________________________
support-bugzilla mailing list
[hidden email]
https://lists.mozilla.org/listinfo/support-bugzilla
PLEASE put [hidden email] in the To: field when you reply.

smime.p7s (7K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Issues upgrading using Postgres

Emmanuel Seyman-3
* Linn, Steven CTR MDA/DTXI via support-bugzilla [21/01/2020 17:20] :
>
> I am attempting to upgrade my Bugzilla installation to 5.0.6, using Postgres
> 9.2 as my database, on Centos7. My old install is on Centos6, using Postgres
> 8.4. All seems to go well until I import the dump file on the new server. I
> have tried copying the data directory and localconfig from the old to the
> new server, then running the pg_dump to import the dump, but it just doesn't
> seem to be working as advertised.

What you're doing sounds about right.

Once you've run pg_restore on the dump file you've copied to the new server,
are you able to connect to the database using psql and the credentials from
the localconfig file?

If the answer is yes, you need to run checksetup.pl to upgrade the schema of
the database.  If you can't connect, we're going to need what error message
you got to be able to help you further.

Regards,
Emmanuel
_______________________________________________
support-bugzilla mailing list
[hidden email]
https://lists.mozilla.org/listinfo/support-bugzilla
PLEASE put [hidden email] in the To: field when you reply.
Reply | Threaded
Open this post in threaded view
|

RE: Issues upgrading using Postgres

Bugzilla - Users mailing list
In reply to this post by Bugzilla - Users mailing list
Hi Thorsten,

When I run pg_restore, it tells me that the input file appears to be a text
format dump, please use psql. SO that is why I used pg_dump to attempt to
import it. But, when I try to import into a new database, I just get all
kinds of 'invalid command \N' or 'invalid command \.' messages scrolling
across my screen. If I copy the data directory from the old server to the
new server and try to start the database, it tells me that the database
can't start because I have data built on pgsql 8.4, not 9.2...as I would
expect it to say. So, do I need to remove pgsql 9.2 and put 8.4 on, then
upgrade? It seems like there would be an easier way than that.  Would
dumping the db into a different format, so that I could use pg_restore
possibly work better?

Steve Linn
MDDC- Sr. Linux Administrator
Desk: (256)450-0384

-----Original Message-----
From: support-bugzilla <[hidden email]> On
Behalf Of Thorsten Schöning
Sent: Tuesday, January 21, 2020 12:16 PM
To: [hidden email]
Subject: Re: Issues upgrading using Postgres

Guten Tag Linn, Steven CTR MDA/DTXI via support-bugzilla, am Dienstag, 21.
Januar 2020 um 18:20 schrieben Sie:

> [...]I
> have tried copying the data directory and localconfig from the old to
> the new server, then running the pg_dump to import the dump, but it
> just doesn't seem to be working as advertised.[...]

It's unclear to me what is happending exactly. Any errors, not data where
you wuld expect it, something completele different? Please be more detailed.

Besides that, pg_dump creates dumps only, pg_restore loads them into your
Postgres.

https://www.postgresql.org/docs/9.2/app-pgrestore.html

> Can someone give me a step by step on how I should be approaching
> this?

https://bugzilla.readthedocs.io/en/5.0/installing/moving.html

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: [hidden email]
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB
207 694 - Geschäftsführer: Andreas Muchow

_______________________________________________
support-bugzilla mailing list
[hidden email]
https://lists.mozilla.org/listinfo/support-bugzilla
PLEASE put [hidden email] in the To: field when you
reply.

_______________________________________________
support-bugzilla mailing list
[hidden email]
https://lists.mozilla.org/listinfo/support-bugzilla
PLEASE put [hidden email] in the To: field when you reply.

smime.p7s (7K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: Issues upgrading using Postgres

Bugzilla - Users mailing list
Thank you, Thorsten!! I created a new, clean Bugzilla database and was able
to successfully restore all data using pg_restore.

Steve Linn
MDDC- Sr. Linux Administrator
Desk: (256)450-0384


-----Original Message-----
From: support-bugzilla <[hidden email]> On
Behalf Of Thorsten Schöning
Sent: Wednesday, January 22, 2020 9:29 AM
To: [hidden email]
Subject: Re: Issues upgrading using Postgres

Guten Tag Linn, Steven CTR MDA/DTXI via support-bugzilla, am Mittwoch, 22.
Januar 2020 um 15:43 schrieben Sie:

> When I run pg_restore, it tells me that the input file appears to be a
> text format dump, please use psql. SO that is why I used pg_dump to
> attempt to import it.

If it tells you to use psql, using pg_dump instead doesn't make much sense.
It simply doesn't import anything anyway, it only exports in various
formats. Look at its docs, the default is plain SQL, that's why psql was
mentioned, because that is able to read the output on STDIN and stuff. Read
its docs for further usage.

pg_restore is to restore from the binary archive formats pg_Dump is able to
create. I prefer to use the format "c" or "custom", because that compresses
things and creates one file only. Look at the "--format" argument of pg_dump
and pg_restore.

https://www.postgresql.org/docs/9.3/app-pgdump.html
https://www.postgresql.org/docs/9.2/app-pgrestore.html

> But, when I try to import into a new database, I just get all kinds of
> 'invalid command \N' or 'invalid command \.' messages scrolling across
> my screen.

Because pg_dump is simply the wrong approach for imports.

> If I copy the data directory from the old server to the new server and
> try to start the database, it tells me that the database can't start
> because I have data built on pgsql 8.4, not 9.2...as I would expect it
> to say. So, do I need to remove pgsql 9.2 and put 8.4 on, then
> upgrade?

You can't copy Postgres's data dirs like that, you need to upgrade between
major releases. The easiest is to use pg_dump on your old and pg_restore on
your new system with format "c".

https://www.postgresql.org/docs/9.6/pgupgrade.html

> It seems like there would be an easier way than that.  Would dumping
> the db into a different format, so that I could use pg_restore
> possibly work better?

Yes, format "c" most likely.

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: [hidden email]
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB
207 694 - Geschäftsführer: Andreas Muchow

_______________________________________________
support-bugzilla mailing list
[hidden email]
https://lists.mozilla.org/listinfo/support-bugzilla
PLEASE put [hidden email] in the To: field when you
reply.

_______________________________________________
support-bugzilla mailing list
[hidden email]
https://lists.mozilla.org/listinfo/support-bugzilla
PLEASE put [hidden email] in the To: field when you reply.

smime.p7s (7K) Download Attachment