[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: [ADMIN] move dbs from 8.1 to 8.4
From: Marc Fromm <Marc.Fromm () wwu ! edu>
Date: 2013-10-10 21:51:42
Message-ID: 93361C67E4EE844A80935863A4FF4B6C076EECEE () Exch2010MB-2 ! univ ! dir ! wwu ! edu
[Download RAW message or body]
After the last database is created there is a failed connection attempt to the first \
database, which never gets created since it is latin1 encoding. psql:pgdbs:229: \
ERROR: database "wsasea_db" does not exist CREATE DATABASE
psql:pgdbs:233: \connect: FATAL: database "awarding_letter_count" does not exist
I wonder if this connect: Fatal message is what stops the population of the tables \
and data, since on the server running 8.1, after the databases are created the tables \
and data are then populated.
From: Steve Crawford [mailto:scrawford@pinpointresearch.com]
Sent: Thursday, October 10, 2013 1:55 PM
To: Marc Fromm; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] move dbs from 8.1 to 8.4
On 10/10/2013 01:17 PM, Marc Fromm wrote:
I built a new server running centos 6.4 and postgresql 8.4. I backed up all the \
databases from the old server running fedora and postgresql 8.1 using this script. \
#!/bin/bash # Backup all Postgresql databases
# Location of the backup logfile.
logfile="/var/lib/pgsql/backups/logs/pg_back.log"
# Location to place backups.
backup_dir="/var/lib/pgsql/backups"
touch $logfile
timeslot=`date +%m-%d-%Y_%H-%M`
timeinfo=`date '+%T-%F'`
/usr/bin/vacuumdb -azh 127.0.0.1 -U postgres
/usr/bin/pg_dumpall -ch 127.0.0.1 -U postgres | gzip > \
"$backup_dir/postgresql-all-$timeslot-databases.gz" echo "Backup of all databses \
complete at $timeinfo for time slot $timeslot--pg_dumpall." >> $logfile
The new server is running postgresql 8.4.
When I restore the databases, with the commands below, only the databases are \
created, no tables and no data is restored.
* gunzip postgresql-all-10-10-2013_04-07-databases.gz
* psql -U postgres -f postgresql-all-10-10-2013_04-07-databases postgres
The above works between two different servers that both run 8.1.
What am I missing to restore all my databases with tables and data from 8.1 to the \
new 8.4?
Thanks
It would be helpful to see any error messages from the client or the log. But lacking \
that info, my first suggestion is to be sure that you are using pg_dumpall from the \
*new* version of PostgreSQL and *not* trying to restore a dump taken with an old \
version into the new version of the database. It's not clear which versions of the \
clients you are using from your message but I'm guessing this may be part of your \
issue.
The second suggestion is to upgrade to a recent version of PostgreSQL. If you are \
going to the effort of an upgrade it seems like a bit of a waste of time to go to 8.4 \
which is only supported for a few more months when you could move to 9.3 and have a \
version supported for nearly five more years plus all the feature and performance \
improvements that have been made in the several years since 8.4 was released.
Cheers,
Steve
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"> <head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:512689850;
mso-list-type:hybrid;
mso-list-template-ids:1506178450 67698689 67698691 67698693 67698689 67698691 \
67698693 67698689 67698691 67698693;} @list l0:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l1
{mso-list-id:826097246;
mso-list-template-ids:-628311248;}
@list l1:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l1:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:"Courier New";
mso-bidi-font-family:"Times New Roman";}
@list l1:level3
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l1:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l1:level5
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l1:level6
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l1:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l1:level8
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l1:level9
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body bgcolor="white" lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">After the last database is created \
there is a failed connection attempt to the first database, which never gets created \
since it is latin1 encoding.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D">psql:pgdbs:229: ERROR: database "wsasea_db" \
does not exist<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D">CREATE DATABASE<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:#1F497D">psql:pgdbs:233: \connect: FATAL: \
database "awarding_letter_count" does not exist<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p> <p \
class="MsoNormal"><span style="color:#1F497D">I wonder if this connect: Fatal message \
is what stops the population of the tables and data, since on the server running 8.1, \
after the databases are created the tables and data are then \
populated.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D"><o:p> </o:p></span></p> <div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span \
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">From:</span></b><span \
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext"> \
Steve Crawford [mailto:scrawford@pinpointresearch.com] <br>
<b>Sent:</b> Thursday, October 10, 2013 1:55 PM<br>
<b>To:</b> Marc Fromm; pgsql-admin@postgresql.org<br>
<b>Subject:</b> Re: [ADMIN] move dbs from 8.1 to 8.4<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">On 10/10/2013 01:17 PM, Marc Fromm wrote:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal">I built a new server running centos 6.4 and postgresql 8.4. I \
backed up all the databases from the old server running fedora and postgresql 8.1 \
using this script.<o:p></o:p></p> <p \
class="MsoNormal"><b>#!/bin/bash</b><o:p></o:p></p> <p class="MsoNormal"><b># Backup \
all Postgresql databases</b><o:p></o:p></p> <p \
class="MsoNormal"><b> </b><o:p></o:p></p> <p class="MsoNormal"><b># Location of \
the backup logfile.</b><o:p></o:p></p> <p \
class="MsoNormal"><b>logfile="/var/lib/pgsql/backups/logs/pg_back.log"</b><o:p></o:p></p>
<p class="MsoNormal"><b> </b><o:p></o:p></p>
<p class="MsoNormal"><b># Location to place backups.</b><o:p></o:p></p>
<p class="MsoNormal"><b>backup_dir="/var/lib/pgsql/backups"</b><o:p></o:p></p>
<p class="MsoNormal"><b>touch $logfile</b><o:p></o:p></p>
<p class="MsoNormal"><b>timeslot=`date +%m-%d-%Y_%H-%M`</b><o:p></o:p></p>
<p class="MsoNormal"><b>timeinfo=`date '+%T-%F'`</b><o:p></o:p></p>
<p class="MsoNormal"><b> </b><o:p></o:p></p>
<p class="MsoNormal"><b>/usr/bin/vacuumdb -azh 127.0.0.1 -U \
postgres</b><o:p></o:p></p> <p class="MsoNormal"><b>/usr/bin/pg_dumpall -ch 127.0.0.1 \
-U postgres | gzip > \
"$backup_dir/postgresql-all-$timeslot-databases.gz"</b><o:p></o:p></p> <p \
class="MsoNormal"><b>echo "Backup of all databses complete at $timeinfo for time \
slot $timeslot--pg_dumpall." >> $logfile</b><o:p></o:p></p> <p \
class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal">The new server is \
running postgresql 8.4. <o:p></o:p></p> <p class="MsoNormal">When I restore the \
databases, with the commands below, only the databases are created, no tables and no \
data is restored.<o:p></o:p></p> <ul style="margin-top:0in" type="disc">
<ul style="margin-top:0in" type="circle">
<li class="MsoNormal" style="mso-list:l0 level2 lfo3"><b>gunzip \
postgresql-all-10-10-2013_04-07-databases.gz</b><o:p></o:p></li><li class="MsoNormal" \
style="mso-list:l0 level2 lfo3"><b>psql -U postgres -f \
postgresql-all-10-10-2013_04-07-databases postgres</b><o:p></o:p></li></ul> </ul>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">The above works between two different servers that both run \
8.1.<o:p></o:p></p> <p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">What am I missing to restore all my databases with tables and \
data from 8.1 to the new 8.4?<o:p></o:p></p> <p \
class="MsoNormal"> <o:p></o:p></p> <p class="MsoNormal">Thanks<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
</blockquote>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span \
style="font-size:12.0pt;font-family:"Times New Roman","serif"">It \
would be helpful to see any error messages from the client or the log. But lacking \
that info, my first suggestion is to be sure that you are using pg_dumpall from the \
*new* version of PostgreSQL and *not* trying to restore a dump taken with an old \
version into the new version of the database. It's not clear which versions of the \
clients you are using from your message but I'm guessing this may be part of your \
issue.<br> <br>
The second suggestion is to upgrade to a recent version of PostgreSQL. If you are \
going to the effort of an upgrade it seems like a bit of a waste of time to go to 8.4 \
which is only supported for a few more months when you could move to 9.3 and have a \
version supported for nearly five more years plus all the feature and performance \
improvements that have been made in the several years since 8.4 was released.<br> \
<br> Cheers,<br>
Steve<o:p></o:p></span></p>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic