pg_upgrade & tablespaces
Hello,
I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade
utility. I need to use pg_upgrade because my production database is 800GB+
and with over 80 tablespaces and doing an export from 9.0 and importing to
9.3 would take at least 2 days.
Currently I am testing on the development database which is only 100GB with
a same number of tablespaces. I am working on FreeBSD with jails. So one
jail contains 9.0 and the other 9.3. In the 93 jail I mount the data and
binary directories for the 9.0 jail.
Here is the command to check:
pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/
-d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c
As you can see the data and binary files for 9.0 are in
/home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3 resides
in the default location.
When running the check it reports that both clusters are compatible. Once
the actual process starts it will work fine until it starts up the 9.3 to
copy data over. The problem that I am having is that pg_upgrade is creating
the 93XXXX files under the old directory and not the new one. So when 9.3
goes to import it doesn't find anything.
Now, both versions can't share the same /data directory for obvious
reasons. Is there any way to make pg_upgrade actually export the new 9.3
files into the 9.3 directory supplied in the pg_upgrade command? I am also
open to any other upgrade ideas.
Thanks,
Joseph
On 12/19/2013 08:34 AM, Joseph Kregloh wrote:
Hello,
I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade
utility. I need to use pg_upgrade because my production database is
800GB+ and with over 80 tablespaces and doing an export from 9.0 and
importing to 9.3 would take at least 2 days.Currently I am testing on the development database which is only 100GB
with a same number of tablespaces. I am working on FreeBSD with jails.
So one jail contains 9.0 and the other 9.3. In the 93 jail I mount the
data and binary directories for the 9.0 jail.Here is the command to check:
pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/
-d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c
The only thing I have is, are the port numbers correct? I tend to use
larger numbers for newer versions which, is why I am asking.
As you can see the data and binary files for 9.0 are in
/home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3
resides in the default location.When running the check it reports that both clusters are compatible.
Once the actual process starts it will work fine until it starts up the
9.3 to copy data over. The problem that I am having is that pg_upgrade
is creating the 93XXXX files under the old directory and not the new
one. So when 9.3 goes to import it doesn't find anything.Now, both versions can't share the same /data directory for obvious
reasons. Is there any way to make pg_upgrade actually export the new 9.3
files into the 9.3 directory supplied in the pg_upgrade command? I am
also open to any other upgrade ideas.Thanks,
Joseph
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Yes, the port numbers are correct. Both instances start by themselves on
their own jails.
On Thu, Dec 19, 2013 at 11:52 AM, Adrian Klaver <adrian.klaver@gmail.com>wrote:
Show quoted text
On 12/19/2013 08:34 AM, Joseph Kregloh wrote:
Hello,
I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade
utility. I need to use pg_upgrade because my production database is
800GB+ and with over 80 tablespaces and doing an export from 9.0 and
importing to 9.3 would take at least 2 days.Currently I am testing on the development database which is only 100GB
with a same number of tablespaces. I am working on FreeBSD with jails.
So one jail contains 9.0 and the other 9.3. In the 93 jail I mount the
data and binary directories for the 9.0 jail.Here is the command to check:
pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/
-d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -cThe only thing I have is, are the port numbers correct? I tend to use
larger numbers for newer versions which, is why I am asking.As you can see the data and binary files for 9.0 are in
/home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3
resides in the default location.When running the check it reports that both clusters are compatible.
Once the actual process starts it will work fine until it starts up the
9.3 to copy data over. The problem that I am having is that pg_upgrade
is creating the 93XXXX files under the old directory and not the new
one. So when 9.3 goes to import it doesn't find anything.Now, both versions can't share the same /data directory for obvious
reasons. Is there any way to make pg_upgrade actually export the new 9.3
files into the 9.3 directory supplied in the pg_upgrade command? I am
also open to any other upgrade ideas.Thanks,
Joseph--
Adrian Klaver
adrian.klaver@gmail.com
On 13-12-19 11:34 AM, Joseph Kregloh wrote:
Hello,
I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade
utility. I need to use pg_upgrade because my production database is
800GB+ and with over 80 tablespaces and doing an export from 9.0 and
importing to 9.3 would take at least 2 days.Currently I am testing on the development database which is only 100GB
with a same number of tablespaces. I am working on FreeBSD with jails.
So one jail contains 9.0 and the other 9.3. In the 93 jail I mount the
data and binary directories for the 9.0 jail.Here is the command to check:
pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/
-d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -cAs you can see the data and binary files for 9.0 are in
/home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3
resides in the default location.When running the check it reports that both clusters are compatible.
Once the actual process starts it will work fine until it starts up
the 9.3 to copy data over. The problem that I am having is that
pg_upgrade is creating the 93XXXX files under the old directory and
not the new one. So when 9.3 goes to import it doesn't find anything.Now, both versions can't share the same /data directory for obvious
reasons. Is there any way to make pg_upgrade actually export the new
9.3 files into the 9.3 directory supplied in the pg_upgrade command? I
am also open to any other upgrade ideas.Thanks,
Joseph
Hi Joseph,
Can you post your actual command syntax when you run the upgrade (not
the check)? Maybe there'll be something wrong there we can spot.
When I did it recently, I used something along the lines of:
(PG93path)/pg_upgrade -d /opt/rg/data/pgsql90 -D /opt/rg/data/pgsql93 -b
/(path to PG90 binaries)/bin -B /(path to 93 binaries) -v -p (oldport)
-P (newport)
Ziggy
Here is the output of my last test run:
[pgsql@postgres-93-upgrade ~]$ time pg_upgrade -b /home/jkregloh/pg_bin/ -B
/usr/local/bin/ -d /home/jkregloh/pg_data/data -D /usr/local/pgsql/data/ -p
5452 -P 5451
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID for new cluster ok
Setting oldest multixact ID on new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Restoring global objects in the new cluster ok
Adding support functions to new cluster ok
Restoring database schemas in the new cluster
ok
Removing support functions from new cluster ok
Copying user relation files
...l/data/drupal_dbspace/PG_9.0_201008051/2752430/10913518
error while copying relation "pg_catalog.pg_largeobject"
("/usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051/2752430/10913518"
to "/usr/local/pgsql/data/drupal_dbspace/PG_9.3_201306121/16499/12301"): No
such file or directory
Failure, exiting
real 2m10.913s
user 0m5.691s
sys 0m10.525s
--------------------------
Listing of that directory in the 9.0 folder:
[pgsql@postgres-93-upgrade ~]$ ls -la
/home/jkregloh/pg_data/data/drupal_dbspace/
total 19
drwx------ 4 pgsql pgsql 4 Jun 8 2013 .
drwx------ 38 pgsql pgsql 46 Dec 19 20:18 ..
drwx------ 4 pgsql pgsql 4 Oct 20 2011 PG_9.0_201008051
--------------------------
Listing of that directory in the 9.3 folder:
[pgsql@postgres-93-upgrade ~]$ ls -ls /usr/local/pgsql/data/drupal_dbspace/
total 4
4 drwx------ 3 pgsql pgsql 3 Dec 19 20:18 PG_9.3_201306121
So what I get from this is that it does create the correct 9.3 files in the
new location, however it cannot copy the relation over because the old data
is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in
/usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to do.
-Joseph
On Thu, Dec 19, 2013 at 12:02 PM, Ziggy Skalski <zskalski@afilias.info>wrote:
Show quoted text
On 13-12-19 11:34 AM, Joseph Kregloh wrote:
Hello,
I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade
utility. I need to use pg_upgrade because my production database is 800GB+
and with over 80 tablespaces and doing an export from 9.0 and importing to
9.3 would take at least 2 days.Currently I am testing on the development database which is only 100GB
with a same number of tablespaces. I am working on FreeBSD with jails. So
one jail contains 9.0 and the other 9.3. In the 93 jail I mount the data
and binary directories for the 9.0 jail.Here is the command to check:
pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/
-d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -cAs you can see the data and binary files for 9.0 are in
/home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3 resides
in the default location.When running the check it reports that both clusters are compatible.
Once the actual process starts it will work fine until it starts up the 9.3
to copy data over. The problem that I am having is that pg_upgrade is
creating the 93XXXX files under the old directory and not the new one. So
when 9.3 goes to import it doesn't find anything.Now, both versions can't share the same /data directory for obvious
reasons. Is there any way to make pg_upgrade actually export the new 9.3
files into the 9.3 directory supplied in the pg_upgrade command? I am also
open to any other upgrade ideas.Thanks,
JosephHi Joseph,
Can you post your actual command syntax when you run the upgrade (not the
check)? Maybe there'll be something wrong there we can spot.
When I did it recently, I used something along the lines of:(PG93path)/pg_upgrade -d /opt/rg/data/pgsql90 -D /opt/rg/data/pgsql93 -b
/(path to PG90 binaries)/bin -B /(path to 93 binaries) -v -p (oldport) -P
(newport)Ziggy
On 12/19/2013 12:27 PM, Joseph Kregloh wrote:
Here is the output of my last test run:
So what I get from this is that it does create the correct 9.3 files in
the new location, however it cannot copy the relation over because the
old data is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in
/usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to do.
So what does mount show?
-Joseph
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I'm not sure what you mean by that question.
-Joseph
On Thu, Dec 19, 2013 at 3:41 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:
Show quoted text
On 12/19/2013 12:27 PM, Joseph Kregloh wrote:
Here is the output of my last test run:
So what I get from this is that it does create the correct 9.3 files in
the new location, however it cannot copy the relation over because the
old data is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in
/usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to
do.So what does mount show?
-Joseph
--
Adrian Klaver
adrian.klaver@gmail.com
On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
<jkregloh@sproutloud.com> wrote:
So what I get from this is that it does create the correct 9.3 files in the
new location, however it cannot copy the relation over because the old data
is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in
/usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to do.
Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints, please?
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[pgsql@postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/
lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 11047389 ->
/home/jkregloh/pg_data/data/stats_dbspace
lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 11047390 ->
/home/jkregloh/pg_data/data/stats_indexspace
lrwxr-xr-x 1 pgsql pgsql 49 Dec 19 19:53 11047391 ->
/home/jkregloh/pg_data/data/stats_staging_dbspace
lrwxr-xr-x 1 pgsql pgsql 52 Dec 19 19:53 11047392 ->
/home/jkregloh/pg_data/data/stats_staging_indexspace
lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 22319 ->
/home/jkregloh/pg_data/data/datapipe_dbspace
lrwxr-xr-x 1 pgsql pgsql 47 Dec 19 19:53 22320 ->
/home/jkregloh/pg_data/data/datapipe_indexspace
lrwxr-xr-x 1 pgsql pgsql 46 Dec 19 19:53 22321 ->
/home/jkregloh/pg_data/data/datapipe_zlogspace
lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 22322 ->
/home/jkregloh/pg_data/data/p3_basic_dbspace
lrwxr-xr-x 1 pgsql pgsql 47 Dec 19 19:53 22323 ->
/home/jkregloh/pg_data/data/p3_basic_indexspace
lrwxr-xr-x 1 pgsql pgsql 38 Dec 19 19:53 22324 ->
/home/jkregloh/pg_data/data/p3_dbspace
lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 22325 ->
/home/jkregloh/pg_data/data/p3_indexspace
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 22326 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_001
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 22327 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_001
lrwxr-xr-x 1 pgsql pgsql 43 Dec 19 19:53 22328 ->
/home/jkregloh/pg_data/data/p3_zlog_dbspace
lrwxr-xr-x 1 pgsql pgsql 46 Dec 19 19:53 22329 ->
/home/jkregloh/pg_data/data/p3_zlog_indexspace
lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 22330 ->
/home/jkregloh/pg_data/data/sling_dbspace
lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 22331 ->
/home/jkregloh/pg_data/data/sling_indexspace
lrwxr-xr-x 1 pgsql pgsql 51 Dec 19 19:53 2260532 ->
/home/jkregloh/pg_data/data/p3_olap_staging_dbspace
lrwxr-xr-x 1 pgsql pgsql 54 Dec 19 19:53 2260533 ->
/home/jkregloh/pg_data/data/p3_olap_staging_indexspace
lrwxr-xr-x 1 pgsql pgsql 52 Dec 19 19:53 2283998 ->
/home/jkregloh/pg_data/data/p3_olap_datamart_dbspace
lrwxr-xr-x 1 pgsql pgsql 55 Dec 19 19:53 2283999 ->
/home/jkregloh/pg_data/data/p3_olap_datamart_indexspace
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327012 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_002
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327013 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_003
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327014 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_004
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327015 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_005
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327016 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_006
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327017 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_007
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327018 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_008
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327019 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_009
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327020 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_010
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327021 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_002
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327022 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_003
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327023 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_004
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327024 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_005
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327025 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_006
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327026 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_007
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327027 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_008
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327028 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_009
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327029 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_010
lrwxr-xr-x 1 pgsql pgsql 42 Dec 19 19:53 2752416 ->
/home/jkregloh/pg_data/data/drupal_dbspace
lrwxr-xr-x 1 pgsql pgsql 45 Dec 19 19:53 2796385 ->
/home/jkregloh/pg_data/data/drupal_indexspace
lrwxr-xr-x 1 pgsql pgsql 55 Dec 19 19:53 5819045 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/january
lrwxr-xr-x 1 pgsql pgsql 56 Dec 19 19:53 5819046 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/february
lrwxr-xr-x 1 pgsql pgsql 53 Dec 19 19:53 5819047 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/march
lrwxr-xr-x 1 pgsql pgsql 53 Dec 19 19:53 5819048 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/april
lrwxr-xr-x 1 pgsql pgsql 51 Dec 19 19:53 5819049 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/may
lrwxr-xr-x 1 pgsql pgsql 52 Dec 19 19:53 5819050 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/june
lrwxr-xr-x 1 pgsql pgsql 52 Dec 19 19:53 5819051 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/july
lrwxr-xr-x 1 pgsql pgsql 54 Dec 19 19:53 5819052 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/august
lrwxr-xr-x 1 pgsql pgsql 57 Dec 19 19:53 5819053 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/september
lrwxr-xr-x 1 pgsql pgsql 55 Dec 19 19:53 5819054 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/october
lrwxr-xr-x 1 pgsql pgsql 56 Dec 19 19:53 5819055 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/november
lrwxr-xr-x 1 pgsql pgsql 56 Dec 19 19:53 5819056 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/december
lrwxr-xr-x 1 pgsql pgsql 58 Dec 19 19:53 5819057 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/january
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 5819058 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/february
lrwxr-xr-x 1 pgsql pgsql 56 Dec 19 19:53 5819059 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/march
lrwxr-xr-x 1 pgsql pgsql 56 Dec 19 19:53 5819060 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/april
lrwxr-xr-x 1 pgsql pgsql 54 Dec 19 19:53 5819061 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/may
lrwxr-xr-x 1 pgsql pgsql 55 Dec 19 19:53 5819063 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/june
lrwxr-xr-x 1 pgsql pgsql 55 Dec 19 19:53 5819064 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/july
lrwxr-xr-x 1 pgsql pgsql 57 Dec 19 19:53 5819065 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/august
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 5819066 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/september
lrwxr-xr-x 1 pgsql pgsql 58 Dec 19 19:53 5819067 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/october
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 5819068 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/november
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 5819070 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/december
lrwxr-xr-x 1 pgsql pgsql 43 Dec 19 19:53 764614 ->
/home/jkregloh/pg_data/data/clients_dbspace
lrwxr-xr-x 1 pgsql pgsql 46 Dec 19 19:53 764617 ->
/home/jkregloh/pg_data/data/clients_indexspace
lrwxr-xr-x 1 pgsql pgsql 45 Dec 19 19:53 764620 ->
/home/jkregloh/pg_data/data/clients_zlogspace
lrwxr-xr-x 1 pgsql pgsql 47 Dec 19 19:53 9296296 ->
/home/jkregloh/pg_data/data/clients_report_data
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669440 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2006
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669441 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2007
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669442 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2008
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669443 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2009
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669444 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2010
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669445 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2011
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669446 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2012
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669447 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2013
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669448 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2014
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669449 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2015
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669450 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2006
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669451 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2007
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669452 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2008
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669453 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2009
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669454 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2010
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669455 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2011
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669456 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2012
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669457 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2013
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669458 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2014
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669459 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2015
-Joseph
On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
Show quoted text
On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
<jkregloh@sproutloud.com> wrote:So what I get from this is that it does create the correct 9.3 files in
the
new location, however it cannot copy the relation over because the old
data
is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in
/usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries todo.
Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints,
please?--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBAhttp://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com
On 12/19/2013 12:46 PM, Joseph Kregloh wrote:
I'm not sure what you mean by that question.
When you run the mount command in the jail what does it show?
-Joseph
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Within the jail it would be:
[pgsql@postgres-93-upgrade ~]$ mount
sata-data/usr/jails/postgres-93-upgrade on / (zfs, local, nfsv4acls)
But I am mounting those directories from the host, which will be:
[root@v1 /postgres_data/p3-dev-db-93]# mount -l | grep postgres-93-upgrade
sata-data/usr/jails/postgres-93-upgrade on /usr/jails/postgres-93-upgrade
(zfs, local, nfsv4acls)
/usr/jails/basejail on /usr/jails/postgres-93-upgrade/basejail (nullfs,
local, read-only)
devfs on /usr/jails/postgres-93-upgrade/dev (devfs, local, multilabel)
fdescfs on /usr/jails/postgres-93-upgrade/dev/fd (fdescfs)
procfs on /usr/jails/postgres-93-upgrade/proc (procfs, local)
/usr/jails/postgres-90-upgrade/usr/local/bin on
/usr/jails/postgres-93-upgrade/home/jkregloh/pg_bin (nullfs, local)
/dev_db/stop_db/postgres_data on
/usr/jails/postgres-93-upgrade/home/jkregloh/pg_data/data (nullfs, local)
/dev_db/stop_db/postgres_archive_data on
/usr/jails/postgres-93-upgrade/home/jkregloh/pg_data/data_archive (nullfs,
local)
On Thu, Dec 19, 2013 at 3:49 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:
Show quoted text
On 12/19/2013 12:46 PM, Joseph Kregloh wrote:
I'm not sure what you mean by that question.
When you run the mount command in the jail what does it show?
-Joseph
--
Adrian Klaver
adrian.klaver@gmail.com
On Thu, Dec 19, 2013 at 11:34:24AM -0500, Joseph Kregloh wrote:
Hello,
I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade utility. I
need to use pg_upgrade because my production database is 800GB+ and with over
80 tablespaces and doing an export from 9.0 and importing to 9.3 would take at
least 2 days.Currently I am testing on the development database which is only 100GB with a
same number of tablespaces. I am working on FreeBSD with jails. So one jail
contains 9.0 and the other 9.3. In the 93 jail I mount the data and binary
directories for the 9.0 jail.
Why don't you do run pg_upgrade in the same jail then just move the
files over to the new jail? That should work better. I am unclear how
a cross-jail upgrade would work at all.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 12/19/2013 12:53 PM, Bruce Momjian wrote:
Currently I am testing on the development database which is only 100GB with a
same number of tablespaces. I am working on FreeBSD with jails. So one jail
contains 9.0 and the other 9.3. In the 93 jail I mount the data and binary
directories for the 9.0 jail.Why don't you do run pg_upgrade in the same jail then just move the
files over to the new jail? That should work better. I am unclear how
a cross-jail upgrade would work at all.
or just leave the 9.3 in the 'postgres' jail, which to me makes as much
sense as anything.
80 tablespaces is a mess no matter how you slice it.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
It's easier to keep things segregated. It is not anymore different than
doing the upgrade in the same jail. Which at the end of the day you are
doing the upgrade in the same jail, because at the end of the day
pg_upgrade just needs the old data an binary to start and create some dump
files.
But the real problem here is with the table spaces. Because in order to
copy the relation over I would need to mount the old data to the
/usr/local/pgsql/data on the new jail. The relation would be there and
would finish successfully(I did this exercise). However the 9.3 install
would be in a different directory, say /usr/local/pgsql_93 and will not
have the data files because they now live in the old install location.
-Joseph
On Thu, Dec 19, 2013 at 3:53 PM, Bruce Momjian <bruce@momjian.us> wrote:
Show quoted text
On Thu, Dec 19, 2013 at 11:34:24AM -0500, Joseph Kregloh wrote:
Hello,
I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade
utility. I
need to use pg_upgrade because my production database is 800GB+ and with
over
80 tablespaces and doing an export from 9.0 and importing to 9.3 would
take at
least 2 days.
Currently I am testing on the development database which is only 100GB
with a
same number of tablespaces. I am working on FreeBSD with jails. So one
jail
contains 9.0 and the other 9.3. In the 93 jail I mount the data and
binary
directories for the 9.0 jail.
Why don't you do run pg_upgrade in the same jail then just move the
files over to the new jail? That should work better. I am unclear how
a cross-jail upgrade would work at all.--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com+ Everyone has their own god. +
On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh
<jkregloh@sproutloud.com> wrote:
On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
<jkregloh@sproutloud.com> wrote:So what I get from this is that it does create the correct 9.3 files in
the
new location, however it cannot copy the relation over because the old
data
is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in
/usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to
do.Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints,
please?[pgsql@postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/
lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 11047389 ->
/home/jkregloh/pg_data/data/stats_dbspace
lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 11047390 ->
/home/jkregloh/pg_data/data/stats_indexspace
lrwxr-xr-x 1 pgsql pgsql 49 Dec 19 19:53 11047391 ->
/home/jkregloh/pg_data/data/stats_staging_dbspace
Bruce, may be it's a silly question, but the above makes me think so.
I always keep tablespaces in locations different from the main data
dir, and never faced something like this.
Doesn't pg_upgrade do a stright replace of -d dir with -D dir
everywhere in paths?
ps. Joseph, please, don't use top-posting, see
http://en.wikipedia.org/wiki/Posting_style#Interleaved_style.
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 12/19/2013 1:06 PM, Joseph Kregloh wrote:
It's easier to keep things segregated. It is not anymore different
than doing the upgrade in the same jail. Which at the end of the day
you are doing the upgrade in the same jail, because at the end of the
day pg_upgrade just needs the old data an binary to start and create
some dump files.
pg_upgrade needs to access the old data AND all the tablespaces at the
same paths as the old server sees them AND the new data and tablespaces
at the same path as the NEW server sees them. if the two servers are
in different jails, I don't see how you could make that work... if you
run pg_upgrade in the host system, then all the paths are different for
both sets of data and tablespaces.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 12/19/2013 01:06 PM, Joseph Kregloh wrote:
It's easier to keep things segregated. It is not anymore different than
doing the upgrade in the same jail. Which at the end of the day you are
doing the upgrade in the same jail, because at the end of the day
pg_upgrade just needs the old data an binary to start and create some
dump files.But the real problem here is with the table spaces. Because in order to
copy the relation over I would need to mount the old data to the
/usr/local/pgsql/data on the new jail. The relation would be there and
would finish successfully(I did this exercise). However the 9.3 install
would be in a different directory, say /usr/local/pgsql_93 and will not
have the data files because they now live in the old install location.
Not sure all of this but I do have this question:
In your original post you have:
pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/
-d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c
Note: -d /home/jkregloh/pg_data/
In your mount info you have:
/dev_db/stop_db/postgres_data on
/usr/jails/postgres-93-upgrade/home/jkregloh/pg_data/data (nullfs, local)
If I am following correctly should it not be:
-d /home/jkregloh/pg_data/data
-Joseph
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Dec 19, 2013 at 01:08:18PM -0800, Sergey Konoplev wrote:
On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh
<jkregloh@sproutloud.com> wrote:On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
<jkregloh@sproutloud.com> wrote:So what I get from this is that it does create the correct 9.3 files in
the
new location, however it cannot copy the relation over because the old
data
is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in
/usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to
do.Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints,
please?[pgsql@postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/
lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 11047389 ->
/home/jkregloh/pg_data/data/stats_dbspace
lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 11047390 ->
/home/jkregloh/pg_data/data/stats_indexspace
lrwxr-xr-x 1 pgsql pgsql 49 Dec 19 19:53 11047391 ->
/home/jkregloh/pg_data/data/stats_staging_dbspaceBruce, may be it's a silly question, but the above makes me think so.
I always keep tablespaces in locations different from the main data
dir, and never faced something like this.Doesn't pg_upgrade do a stright replace of -d dir with -D dir
everywhere in paths?
pg_upgrade is looking at the data dir, the database oid, and relfilenode
to get the old path, and does the same for the new path. Tablespaces
point to the same location in old and new clusters --- only a
subdirectory PG_VERISON is different.
Is /home/jkregloh/pg_data/data also your default cluster directory? If
so, having tablespaces inside of there will not work well as they will
continue to be stored in the old cluster's data directory. Those will
not be renamed/relocated by pg_upgrade.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Dec 19, 2013 at 01:14:15PM -0800, John R Pierce wrote:
On 12/19/2013 1:06 PM, Joseph Kregloh wrote:
It's easier to keep things segregated. It is not anymore different
than doing the upgrade in the same jail. Which at the end of the
day you are doing the upgrade in the same jail, because at the end
of the day pg_upgrade just needs the old data an binary to start
and create some dump files.pg_upgrade needs to access the old data AND all the tablespaces at
the same paths as the old server sees them AND the new data and
tablespaces at the same path as the NEW server sees them. if the
two servers are in different jails, I don't see how you could make
that work... if you run pg_upgrade in the host system, then all the
paths are different for both sets of data and tablespaces.
The big question is should pg_upgrade be checking for this situation in
--check mode, and if so, what should it check for?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Dec 19, 2013 at 1:18 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Thu, Dec 19, 2013 at 01:08:18PM -0800, Sergey Konoplev wrote:
On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh
<jkregloh@sproutloud.com> wrote:On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints,
please?[pgsql@postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/
lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 11047389 ->
/home/jkregloh/pg_data/data/stats_dbspaceDoesn't pg_upgrade do a stright replace of -d dir with -D dir
everywhere in paths?pg_upgrade is looking at the data dir, the database oid, and relfilenode
to get the old path, and does the same for the new path. Tablespaces
point to the same location in old and new clusters --- only a
subdirectory PG_VERISON is different.Is /home/jkregloh/pg_data/data also your default cluster directory? If
so, having tablespaces inside of there will not work well as they will
continue to be stored in the old cluster's data directory. Those will
not be renamed/relocated by pg_upgrade.
The thing is that /home/jkregloh/pg_data/data is his 9.0's cluster
directory and /usr/local/pgsql/data/ is 9.3's one. And pg_upgrade
tries to copy /usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051/2752430/10913518"
to /usr/local/pgsql/data/drupal_dbspace/PG_9.3_201306121/16499/12301.
In other words pg_upgrade thinks that the old tablespace is located in
the same cluster directory as the new one. That made me think that it
just replaces the cluster directory subpath everywhere.
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
