Обсуждение: Server/Data Migration Advice
I've finally received my new virtual database server (Debian Linux) & this weekend I'll be rolling my new PostgreSQL server online. My question is I've never migrated production data from 8.4.8 to 9.1.1. I would like to find out from the community what exactly is the recommended process in moving my database server from 8.4.8 to 9.1.1. I'm not simply upgrading the existing server as I will be installing PostgreSQL 9.1 on the new hardware and not sure if it's as simple as simply performing a pg_dumpall: pg_dumpall > mydata.sql I don't know if I need to use some kind of conversion tool to convert the data from 8.4.8 so that it's compatible with 9.1.1 so if you guys could please shine in on any recommendations, I would greatly appreciate it!
Carlos Mennens <carlos.mennens@gmail.com> wrote: > I've finally received my new virtual database server (Debian Linux) & > this weekend I'll be rolling my new PostgreSQL server online. My > question is I've never migrated production data from 8.4.8 to 9.1.1. I > would like to find out from the community what exactly is the > recommended process in moving my database server from 8.4.8 to 9.1.1. > I'm not simply upgrading the existing server as I will be installing > PostgreSQL 9.1 on the new hardware and not sure if it's as simple as > simply performing a pg_dumpall: > > pg_dumpall > mydata.sql > > I don't know if I need to use some kind of conversion tool to convert > the data from 8.4.8 so that it's compatible with 9.1.1 so if you guys > could please shine in on any recommendations, I would greatly > appreciate it! - you should use 9.1.2, not 9.1.1 ;-) - use the pg_dumpall from the new version to make the dump, for instance pg_dumpall -h <old_host> ... | psql (something like this, on the new host) Du you have BYTEA-Columns? The default output-format changed. Some other details changed too, read the release notes! Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Le 12 décembre 2011 18:12, Carlos Mennens <carlos.mennens@gmail.com> a écrit : > I've finally received my new virtual database server (Debian Linux) & > this weekend I'll be rolling my new PostgreSQL server online. My > question is I've never migrated production data from 8.4.8 to 9.1.1. I > would like to find out from the community what exactly is the > recommended process in moving my database server from 8.4.8 to 9.1.1. > I'm not simply upgrading the existing server as I will be installing > PostgreSQL 9.1 on the new hardware and not sure if it's as simple as > simply performing a pg_dumpall: > > pg_dumpall > mydata.sql it is as simple. You *must* use the pg_dump from 9.1. See : http://www.postgresql.org/docs/9.1/static/upgrading.html > > I don't know if I need to use some kind of conversion tool to convert > the data from 8.4.8 so that it's compatible with 9.1.1 so if you guys > could please shine in on any recommendations, I would greatly > appreciate it! > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
On Mon, Dec 12, 2011 at 12:23 PM, Andreas Kretschmer <akretschmer@spamfence.net> wrote: > - you should use 9.1.2, not 9.1.1 ;-) I don't think it's available yet in Debian repositories. I can only use whatever packages they've compiled in their repositories. > - use the pg_dumpall from the new version to make the dump, for instance > pg_dumpall -h <old_host> ... | psql (something like this, on the new > host) So it would look like this from my new server? pg_dumpall -h old_db > mydata.sql > Du you have BYTEA-Columns? The default output-format changed. Some other > details changed too, read the release notes! I don't think I have any BYTEA columns so I think I'm OK. I'll read the releasenotes...
On Mon, Dec 12, 2011 at 12:23 PM, Andreas Kretschmer <akretschmer@spamfence.net> wrote: > - use the pg_dumpall from the new version to make the dump, for instance > pg_dumpall -h <old_host> ... | psql (something like this, on the new > host) I performed a pg_dump from my new 9.1.2 server but my question now is importing the data. So I had 9.1.2 perform the dump and it's now on the new server but what are (if any) the steps to restore or install them into my new 9.1.2 server? Is there anything I need to do to the dump .sql file before using psql to restore the dump file? pg_dump -h old_db > old_db.sql That command above was the command I issued from new_db (new server) to dump all the 8.4.8 data onto my 9.1.2 machine. What is the next course of action? Is there a conversion process or something I need to do or simply restore it? Can I simply do: psql webmail < old_db.sql Is that sufficient? > Du you have BYTEA-Columns? The default output-format changed. Some other > details changed too, read the release notes! Is there a way in psql client I can search my database tables for any BYTEA columns?
Hi!
-- Is there a way in psql client I can search my database tables for any
BYTEA columns?
What about just grepping your dump file for 'bytea'? :)
Bèrto
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
On Thursday, December 15, 2011 5:33:17 am Carlos Mennens wrote: > I performed a pg_dump from my new 9.1.2 server but my question now is > importing the data. So I had 9.1.2 perform the dump and it's now on > the new server but what are (if any) the steps to restore or install > them into my new 9.1.2 server? Is there anything I need to do to the > dump .sql file before using psql to restore the dump file? > > pg_dump -h old_db > old_db.sql > > That command above was the command I issued from new_db (new server) > to dump all the 8.4.8 data onto my 9.1.2 machine. What is the next > course of action? Is there a conversion process or something I need to > do or simply restore it? Can I simply do: > > psql webmail < old_db.sql > > Is that sufficient? You know the fine manual covers this?: http://www.postgresql.org/docs/9.1/interactive/app-pgdump.html In the case above you seemed to have used the plain text format, in the future should you use a non text format the restore process is here: http://www.postgresql.org/docs/9.1/interactive/app-pgrestore.html -- Adrian Klaver adrian.klaver@gmail.com
On Thu, Dec 15, 2011 at 9:37 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > You know the fine manual covers this?: > http://www.postgresql.org/docs/9.1/interactive/app-pgdump.html I've honestly skimmed the manual and it's very easy to use and good but it covers so many aspects and sometimes I need a specific answer for my situation. > In the case above you seemed to have used the plain text format, in the future > should you use a non text format the restore process is here: > http://www.postgresql.org/docs/9.1/interactive/app-pgrestore.html Are you saying I dumped the database in a text formal or I'm restoring the database infile in plain text? I'll read that 2nd URL link you posted. What are the issues with using pg_dump to dump and then 'psql some_db < pg_dump.sql'? I show the manual explains how to perform this action and what the system is doing but doesn't explain why this is an issue or why you recommended to use pg_restore instead?
On Thursday, December 15, 2011 6:55:46 am Carlos Mennens wrote: > On Thu, Dec 15, 2011 at 9:37 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > > You know the fine manual covers this?: > > http://www.postgresql.org/docs/9.1/interactive/app-pgdump.html > > I've honestly skimmed the manual and it's very easy to use and good > but it covers so many aspects and sometimes I need a specific answer > for my situation. > > > In the case above you seemed to have used the plain text format, in the > > future should you use a non text format the restore process is here: > > http://www.postgresql.org/docs/9.1/interactive/app-pgrestore.html > > Are you saying I dumped the database in a text formal or I'm restoring > the database infile in plain text? I'll read that 2nd URL link you > posted. What are the issues with using pg_dump to dump and then 'psql > some_db < pg_dump.sql'? I show the manual explains how to perform this > action and what the system is doing but doesn't explain why this is an > issue or why you recommended to use pg_restore instead? The reason I pointed to the manual links is that there is a lot of good information in there. It deserves more than a skim:). Realistically, to get the most out of the dump/restore process you need to know the options available on both sides of the procedure. For instance the -C option to pg_dump, puts in command to create database on restore, saves the step of creating a database on the other end. The issues that may arise are most likely going to be generic to the upgrade from 8.4 to 9.1. To get a handle on those it is best to read the Release Notes for the covered versions, in particular the Migration section. In this case the notes for 9.0 that cover the migration issues from 8.4 and the notes for 9.1 that cover same from 9.0. Not all the issues may affect you, it depends on what you have done in your database. What form of pg_dump you use is up to you. I will say, the custom format, -Fc, has some interesting features. One, it is compressed. Two, you can restore from it in full or pick and choose(within reason,see docs) those items you wish to restore without resorting to cut and paste. -- Adrian Klaver adrian.klaver@gmail.com
On Thu, Dec 15, 2011 at 10:28 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > The reason I pointed to the manual links is that there is a lot of good > information in there. It deserves more than a skim:). Realistically, to get the > most out of the dump/restore process you need to know the options available on > both sides of the procedure. For instance the -C option to pg_dump, puts in > command to create database on restore, saves the step of creating a database on > the other end. The issues that may arise are most likely going to be generic to > the upgrade from 8.4 to 9.1. To get a handle on those it is best to read the > Release Notes for the covered versions, in particular the Migration section. In > this case the notes for 9.0 that cover the migration issues from 8.4 and the > notes for 9.1 that cover same from 9.0. Not all the issues may affect you, it > depends on what you have done in your database. What form of pg_dump you use is > up to you. I will say, the custom format, -Fc, has some interesting features. > One, it is compressed. Two, you can restore from it in full or pick and > choose(within reason,see docs) those items you wish to restore without resorting > to cut and paste. On Thu, Dec 15, 2011 at 10:28 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > The reason I pointed to the manual links is that there is a lot of good > information in there. It deserves more than a skim:). Realistically, to get the > most out of the dump/restore process you need to know the options available on > both sides of the procedure. For instance the -C option to pg_dump, puts in > command to create database on restore, saves the step of creating a database on > the other end. The issues that may arise are most likely going to be generic to > the upgrade from 8.4 to 9.1. To get a handle on those it is best to read the > Release Notes for the covered versions, in particular the Migration section. In > this case the notes for 9.0 that cover the migration issues from 8.4 and the > notes for 9.1 that cover same from 9.0. Not all the issues may affect you, it > depends on what you have done in your database. What form of pg_dump you use is > up to you. I will say, the custom format, -Fc, has some interesting features. > One, it is compressed. Two, you can restore from it in full or pick and > choose(within reason,see docs) those items you wish to restore without resorting > to cut and paste. So after reading http://www.postgresql.org/docs/9.1/interactive/backup-dump.html, I'm not sure why the manual shows you in "24.1. SQL Dump" & then directly after in 24.1.1, they explain how to restore with psql as you advised me not to. I got my psql db_name < infile command directly from the manual. I know it's personal preference but from everything you noted, why didn't they just explain how to perform a pg_restore in the "24.1.1. Restoring the Dump" section. "24.1.3. Handling Large Databases" section is very cool but also extremely vague IMO. > Use pg_dump's custom dump format. If PostgreSQL was built on a system with the zlib > compression library installed, the custom dump format will compress data as it writes it to > the output file. This will produce dump file sizes similar to using gzip, but it has the added > advantage that tables can be restored selectively. The following command dumps a > database using the custom dump format: So this seems helpful to myself in only that A: the dump is compressed (my databases are generally small anyways) and B: I don't have to create the database before I restore it. My only question is I see that noted nowhere in the manual ... yet but I'm just wondering if that's a correct statement.
On 15/12/2011 15:57, Carlos Mennens wrote: > So after reading > http://www.postgresql.org/docs/9.1/interactive/backup-dump.html, > > I'm not sure why the manual shows you in "24.1. SQL Dump" & then > directly after in 24.1.1, they explain how to restore with psql as you > advised me not to. I got my psql db_name < infile command directly > from the manual. I know it's personal preference but from everything > you noted, why didn't they just explain how to perform a pg_restore in > the "24.1.1. Restoring the Dump" section. The point here is that with the plain-text dump (the default output from pg_dump), you can feed that directly to psql; but you have no control over what is restored, or in what order, without editing the dump file directly. If, however, you using one of the other output options, you need to use pg_restore; but you can do all sorts of things with the restore. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Thu, Dec 15, 2011 at 11:09 AM, Raymond O'Donnell <rod@iol.ie> wrote: > The point here is that with the plain-text dump (the default output from > pg_dump), you can feed that directly to psql; but you have no control > over what is restored, or in what order, without editing the dump file > directly. > > If, however, you using one of the other output options, you need to use > pg_restore; but you can do all sorts of things with the restore. I think I now understand and sorry. I have not been using PostgreSQL long at all and it's my first ever venture into any RDBMS and even ANSI SQL. So basically: pg_dump = plain text dumps which can be read by 'psql' & 'pg_restore'. pg_dump -Fc = custom / compressed dumps that must be used by pg_restore only with options to pick / choose what I want from the dump. I hope I got that right. I think I've learned a lot from you guys pointing me in the right direction and RTFM.
On Thursday, December 15, 2011 7:57:40 am Carlos Mennens wrote: > So after reading > http://www.postgresql.org/docs/9.1/interactive/backup-dump.html, That was not the link I posted. In fact I have never actually been to that page:) This is the link I posted: http://www.postgresql.org/docs/9.1/interactive/app-pgdump.html > > I'm not sure why the manual shows you in "24.1. SQL Dump" & then > directly after in 24.1.1, they explain how to restore with psql as you > advised me not to. I got my psql db_name < infile command directly > from the manual. I know it's personal preference but from everything > you noted, why didn't they just explain how to perform a pg_restore in > the "24.1.1. Restoring the Dump" section. First I did not advise against using a plain text dump, just noted there are options. You have stumbled across the reason I mentioned the options. One of the quirks of Postgres is that there is one dump command(pg_dump), but two ways to restore (psql, pg_restore), depending on the format of the dump. What is being shown in the section you refer to is the plain text(SQL) method. To restore a plain text dump you need to use psql. You can do it as shown or by using psql -f 'dump.sql' The reason to use -f is found here: http://www.postgresql.org/docs/9.1/interactive/app-psql.html " -f filename --file=filename Use the file filename as the source of commands instead of reading commands interactively. After the file is processed, psql terminates. This is in many ways equivalent to the internal command \i. If filename is - (hyphen), then standard input is read. Using this option is subtly different from writing psql < filename. In general, both will do what you expect, but using -f enables some nice features such as error messages with line numbers. There is also a slight chance that using this option will reduce the start-up overhead. On the other hand, the variant using the shell's input redirection is (in theory) guaranteed to yield exactly the same output you would have received had you entered everything by hand. " If you do one of the non-text dumps then you will need to use pg_restore: http://www.postgresql.org/docs/9.1/interactive/app-pgrestore.html > > "24.1.3. Handling Large Databases" section is very cool but also > extremely vague IMO. > > > Use pg_dump's custom dump format. If PostgreSQL was built on a system > > with the zlib compression library installed, the custom dump format will > > compress data as it writes it to the output file. This will produce dump > > file sizes similar to using gzip, but it has the added advantage that > > tables can be restored selectively. The following command dumps a > > > database using the custom dump format: > So this seems helpful to myself in only that A: the dump is compressed > (my databases are generally small anyways) and B: I don't have to > create the database before I restore it. My only question is I see > that noted nowhere in the manual ... yet but I'm just wondering if > that's a correct statement. It is noted if you go to the pg_dump link shown above:) -- Adrian Klaver adrian.klaver@gmail.com