Postgres Pro
Downloads
Home   >   mailing lists

Re: pg_upgrade & tablespaces - Mailing list pgsql-general

From Joseph Kregloh
Subject Re: pg_upgrade & tablespaces
Date
Msg-id CAAW2xffrcSkfBK6v6B3nbS=NRmUum1m33ysFFmQGQ8vmV8dHDg@mail.gmail.com
Whole thread Raw
In response to Re: pg_upgrade & tablespaces  (Adrian Klaver <adrian.klaver@gmail.com>)
Responses Re: pg_upgrade & tablespaces
List pgsql-general
Tree view

So how are you moving the 9.0 directory?

Just using a mv command like below:
mv /usr/local/pgsql/data /usr/local/pgsql_90/data 

Then I recreated the symlinks in /pg_tbspc to point to the new directory path.

What does a listing for that directory look like after the move?

[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_90/data/
total 97
drwx------  13 pgsql  pgsql     19 Dec 26 21:03 .
drwx------   3 pgsql  pgsql      3 Dec 26 20:43 ..
-rwx------   1 pgsql  pgsql      4 Dec 26 19:30 PG_VERSION
drwx------   5 pgsql  pgsql      5 Dec 26 19:30 base
drwx------   3 pgsql  pgsql      3 Dec 26 19:52 drupal_dbspace
drwx------   2 pgsql  pgsql     43 Dec 26 21:03 global
drwx------   2 pgsql  pgsql      3 Dec 26 19:30 pg_clog
-rwx------   1 pgsql  pgsql   3939 Dec 26 19:30 pg_hba.conf
-rwx------   1 pgsql  pgsql   1636 Dec 26 19:30 pg_ident.conf
drwx------   4 pgsql  pgsql      4 Dec 26 19:30 pg_multixact
drwx------   2 pgsql  pgsql      3 Dec 26 21:02 pg_notify
drwx------   2 pgsql  pgsql      2 Dec 26 21:03 pg_stat_tmp
drwx------   2 pgsql  pgsql      3 Dec 26 19:30 pg_subtrans
drwx------   2 pgsql  pgsql      3 Dec 26 21:00 pg_tblspc
drwx------   2 pgsql  pgsql      2 Dec 26 19:30 pg_twophase
drwx------   3 pgsql  pgsql      6 Dec 26 20:53 pg_xlog
-rwx------   1 pgsql  pgsql  18079 Dec 26 19:43 postgresql.conf
-rwx------   1 pgsql  pgsql     59 Dec 26 19:44 postmaster.log
-rwx------   1 pgsql  pgsql    195 Dec 26 21:02 postmaster.opts
[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_90/data/drupal_dbspace/
total 12
drwx------   3 pgsql  pgsql   3 Dec 26 19:52 .
drwx------  13 pgsql  pgsql  19 Dec 26 21:03 ..
drwx------   3 pgsql  pgsql   3 Dec 26 19:52 PG_9.0_201008051
[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_90/data/pg_tblspc/     
total 9
drwx------   2 pgsql  pgsql   3 Dec 26 21:00 .
drwx------  13 pgsql  pgsql  19 Dec 26 21:03 ..
lrwxr-xr-x   1 pgsql  pgsql  39 Dec 26 21:00 24658 -> /usr/local/pgsql_90/data/drupal_dbspace
 
What does a listing for the 9.3 directory look like?

 This is right after the initdb, I also created the /drupal_dbspace

[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/
total 104
drwx------  16 pgsql  pgsql     20 Dec 27 14:48 .
drwxr-xr-x   3 pgsql  pgsql      5 Dec 26 15:17 ..
-rw-------   1 pgsql  pgsql      4 Dec 27 14:38 PG_VERSION
drwx------   5 pgsql  pgsql      5 Dec 27 14:39 base
drwxr-xr-x   2 pgsql  pgsql      2 Dec 27 14:48 drupal_dbspace
drwx------   2 pgsql  pgsql     42 Dec 27 14:39 global
drwx------   2 pgsql  pgsql      3 Dec 27 14:38 pg_clog
-rw-------   1 pgsql  pgsql   4467 Dec 27 14:38 pg_hba.conf
-rw-------   1 pgsql  pgsql   1636 Dec 27 14:38 pg_ident.conf
drwx------   4 pgsql  pgsql      4 Dec 27 14:38 pg_multixact
drwx------   2 pgsql  pgsql      3 Dec 27 14:39 pg_notify
drwx------   2 pgsql  pgsql      2 Dec 27 14:38 pg_serial
drwx------   2 pgsql  pgsql      2 Dec 27 14:38 pg_snapshots
drwx------   2 pgsql  pgsql      2 Dec 27 14:38 pg_stat
drwx------   2 pgsql  pgsql      2 Dec 27 14:38 pg_stat_tmp
drwx------   2 pgsql  pgsql      3 Dec 27 14:38 pg_subtrans
drwx------   2 pgsql  pgsql      2 Dec 27 14:38 pg_tblspc
drwx------   2 pgsql  pgsql      2 Dec 27 14:38 pg_twophase
drwx------   3 pgsql  pgsql      4 Dec 27 14:38 pg_xlog
-rw-------   1 pgsql  pgsql  20410 Dec 27 14:38 postgresql.conf
[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/drupal_dbspace/
total 8
drwxr-xr-x   2 pgsql  pgsql   2 Dec 27 14:48 .
drwx------  16 pgsql  pgsql  20 Dec 27 14:48 ..
[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/pg_tblspc/      
total 8
drwx------   2 pgsql  pgsql   2 Dec 27 14:38 .
drwx------  16 pgsql  pgsql  20 Dec 27 14:48 ..



So what do the listings for the old and new data directories look like after the upgrade?


Here is the moved 9.0 directory, it's a listing of data, drupal_dbspace, and pg_tblsp

[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_90/data/
total 97
drwx------  13 pgsql  pgsql     19 Dec 26 21:03 .
drwx------   3 pgsql  pgsql      3 Dec 26 20:43 ..
-rwx------   1 pgsql  pgsql      4 Dec 26 19:30 PG_VERSION
drwx------   5 pgsql  pgsql      5 Dec 26 19:30 base
drwx------   3 pgsql  pgsql      3 Dec 26 19:52 drupal_dbspace
drwx------   2 pgsql  pgsql     43 Dec 26 21:03 global
drwx------   2 pgsql  pgsql      3 Dec 26 19:30 pg_clog
-rwx------   1 pgsql  pgsql   3939 Dec 26 19:30 pg_hba.conf
-rwx------   1 pgsql  pgsql   1636 Dec 26 19:30 pg_ident.conf
drwx------   4 pgsql  pgsql      4 Dec 26 19:30 pg_multixact
drwx------   2 pgsql  pgsql      3 Dec 26 21:02 pg_notify
drwx------   2 pgsql  pgsql      2 Dec 26 21:03 pg_stat_tmp
drwx------   2 pgsql  pgsql      3 Dec 26 19:30 pg_subtrans
drwx------   2 pgsql  pgsql      3 Dec 26 21:00 pg_tblspc
drwx------   2 pgsql  pgsql      2 Dec 26 19:30 pg_twophase
drwx------   3 pgsql  pgsql      6 Dec 26 20:53 pg_xlog
-rwx------   1 pgsql  pgsql  18079 Dec 26 19:43 postgresql.conf
-rwx------   1 pgsql  pgsql     59 Dec 26 19:44 postmaster.log
-rwx------   1 pgsql  pgsql    195 Dec 26 21:02 postmaster.opts
[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_90/data/drupal_dbspace/
total 12
drwx------   3 pgsql  pgsql   3 Dec 26 19:52 .
drwx------  13 pgsql  pgsql  19 Dec 26 21:03 ..
drwx------   3 pgsql  pgsql   3 Dec 26 19:52 PG_9.0_201008051
[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_90/data/pg_tblspc/     
total 9
drwx------   2 pgsql  pgsql   3 Dec 26 21:00 .
drwx------  13 pgsql  pgsql  19 Dec 26 21:03 ..
lrwxr-xr-x   1 pgsql  pgsql  39 Dec 26 21:00 24658 -> /usr/local/pgsql_90/data/drupal_dbspace

Here is a listing of the 9.3 directory in the default location, same listings as above:

[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/
total 107
drwx------  16 pgsql  pgsql     21 Dec 26 21:03 .
drwxr-xr-x   3 pgsql  pgsql      5 Dec 26 15:17 ..
-rw-------   1 pgsql  pgsql      4 Dec 26 21:01 PG_VERSION
drwx------   5 pgsql  pgsql      5 Dec 26 21:01 base
drwx------   3 pgsql  pgsql      3 Dec 26 21:03 drupal_dbspace
drwx------   2 pgsql  pgsql     44 Dec 26 21:03 global
drwx------   2 pgsql  pgsql      3 Dec 26 21:03 pg_clog
-rw-------   1 pgsql  pgsql   4467 Dec 26 21:01 pg_hba.conf
-rw-------   1 pgsql  pgsql   1636 Dec 26 21:01 pg_ident.conf
drwx------   4 pgsql  pgsql      4 Dec 26 21:01 pg_multixact
drwx------   2 pgsql  pgsql      3 Dec 26 21:03 pg_notify
drwx------   2 pgsql  pgsql      2 Dec 26 21:01 pg_serial
drwx------   2 pgsql  pgsql      2 Dec 26 21:01 pg_snapshots
drwx------   2 pgsql  pgsql      8 Dec 26 21:03 pg_stat
drwx------   2 pgsql  pgsql      2 Dec 26 21:03 pg_stat_tmp
drwx------   2 pgsql  pgsql      3 Dec 26 21:01 pg_subtrans
drwx------   2 pgsql  pgsql      3 Dec 26 21:03 pg_tblspc
drwx------   2 pgsql  pgsql      2 Dec 26 21:01 pg_twophase
drwx------   3 pgsql  pgsql      5 Dec 26 21:03 pg_xlog
-rw-------   1 pgsql  pgsql  20411 Dec 26 21:01 postgresql.conf
-rw-------   1 pgsql  pgsql    236 Dec 26 21:03 postmaster.opts
[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/drupal_dbspace/
total 12
drwx------   3 pgsql  pgsql   3 Dec 26 21:03 .
drwx------  16 pgsql  pgsql  21 Dec 26 21:03 ..
drwx------   3 pgsql  pgsql   3 Dec 26 21:03 PG_9.3_201306121
[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/pg_tblspc/     
total 9
drwx------   2 pgsql  pgsql   3 Dec 26 21:03 .
drwx------  16 pgsql  pgsql  21 Dec 26 21:03 ..
lrwx------   1 pgsql  pgsql  36 Dec 26 21:03 16420 -> /usr/local/pgsql/data/drupal_dbspace
 
 

pgsql-general by date:

Previous
From: Clemens Eisserer
Date:
Subject: Why is wal_writer_delay limited to 10s?
Next
From: Adrian Klaver
Date:
Subject: Re: pg_upgrade & tablespaces