Hello,
I have installed Postgresql94 using zypper and I would like to change the data directory. I alraedy have a database, so I would like to migrate it to the new location and then change the location in the postgresql configuration.
I Have tried to make the change, but I was unable to manage to restart postgres server again.
Regards,
Marcio
On 09/19/2018 01:04 PM, mlino wrote:[color=blue]
I have installed Postgresql94 using zypper and I would like to change
the data directory. I alraedy have a database, so I would like to
migrate it to the new location and then change the location in the
postgresql configuration.[/color]
That sounds reasonable, assuming by “migrate it to the new location” you
mean something like the following:
I Have tried to make the change, but I was unable to manage to restart
postgres server again.[/color]
How, in annoying detail, did you try this, and what specific symptoms do
you have of failure? The more details you can provide the better,
including configuration files (like for PostgreSQL), log file output, etc.
–
Good luck.
If you find this post helpful and are logged into the web interface,
show your appreciation and click on the star below.
If you want to send me a private message, please let me know in the
forum as I do not use the web interface often.
Hi and thank you.
[QUOTE=ab;54488]On 09/19/2018 01:04 PM, mlino wrote:[color=blue]
I have installed Postgresql94 using zypper and I would like to change
the data directory. I alraedy have a database, so I would like to
migrate it to the new location and then change the location in the
postgresql configuration.[/color]
That sounds reasonable, assuming by “migrate it to the new location” you
mean something like the following:
[color=red]
I followed the exact steps you listed above, but the server doesn’t start in the new location. I got an error in the postgresql-init script, related to the new location of the files.
[/color]
[color=blue]
I Have tried to make the change, but I was unable to manage to restart
postgres server again.[/color]
How, in annoying detail, did you try this, and what specific symptoms do
you have of failure? The more details you can provide the better,
including configuration files (like for PostgreSQL), log file output, etc.
[color=red]
I stopped the server the I did a copy for the new location using rsync
rsync -av -o -g /var/lib/pgsql/ /dados/pgsql
Then I changed the location of the files in /etc/sysconfig/postgresql pointing to the new location. I have left the new location commented as you can see bellow.
POSTGRES_DATADIR=“~postgres/data”
#POSTGRES_DATADIR=“/dados/pgsql/data”
After that, I tried to restart the server and it doesn’t start due to an error in the postgresql-init script. Then I tried to fix the error but I didn’t figure out what was happening so I decided to rollback the changes.
This is the content of the /dados/pgsql
[FONT=Courier New][SIZE=3]
-rwxr-x— 1 postgres postgres 8851 Sep 19 17:37 .bash_history
drwxr-x— 3 postgres postgres 4096 Sep 19 15:06 .local
-rwxr-x— 1 postgres postgres 1314 Sep 19 15:06 .psql_history
-rwxr-x— 1 postgres postgres 600 Sep 19 15:06 .viminfo
-rw-r–r-- 1 postgres postgres 4947 Sep 19 16:25 .y2log
-rwxr-x— 1 postgres postgres 4 Sep 19 15:06 PG_VERSION
drwx------ 8 postgres postgres 4096 Sep 19 15:06 base
drwxr-xr-x 3 postgres postgres 4096 Sep 19 17:41 dados
drwx------ 19 postgres postgres 4096 Sep 19 17:52 data
drwx------ 2 postgres postgres 4096 Sep 19 15:06 global
-rwxr-x— 1 postgres postgres 1341 Sep 19 17:44 initlog
-rwxr-x— 1 postgres postgres 1058 Sep 19 15:06 logfile
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_clog
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_dynshmem
-rwxr-x— 1 postgres postgres 4517 Sep 19 15:06 pg_hba.conf
-rwxr-x— 1 postgres postgres 1636 Sep 19 15:06 pg_ident.conf
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_log
drwx------ 4 postgres postgres 4096 Sep 19 15:06 pg_logical
drwx------ 4 postgres postgres 4096 Sep 19 15:06 pg_multixact
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_notify
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_replslot
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_serial
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_snapshots
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_stat
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_subtrans
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_tblspc
drwx------ 2 postgres postgres 4096 Sep 19 15:06 pg_twophase
drwx------ 3 postgres postgres 4096 Sep 19 15:06 pg_xlog
-rwxr-x— 1 postgres postgres 88 Sep 19 15:06 postgresql.auto.conf
-rwxr-x— 1 postgres postgres 20860 Sep 19 15:06 postgresql.conf
-rwxr-x— 1 postgres postgres 57 Sep 19 15:06 postmaster.opts[/SIZE][/FONT]
[/color]
Good luck.
If you find this post helpful and are logged into the web interface,
show your appreciation and click on the star below.
If you want to send me a private message, please let me know in the
forum as I do not use the web interface often.[/QUOTE]
On 09/20/2018 07:04 AM, mlino wrote:[color=blue]
[color=green]
I stopped the server the I did a copy for the new location using rsync
rsync -av -o -g /var/lib/pgsql/ /dados/pgsql[/color][/color]
rsync is awesome.
[color=blue][color=green]
Then I changed the location of the files in /etc/sysconfig/postgresql
pointing to the new location. I have left the new location commented as
you can see bellow.[/color][/color]
I presume before you rolled back you did not have the new location
commented out. Thanks for not just removing it; that was a good idea.
[color=blue][color=green]
POSTGRES_DATADIR=“~postgres/data”
#POSTGRES_DATADIR=“/dados/pgsql/data”After that, I tried to restart the server and it doesn’t start due to an
error in the postgresql-init script. Then I tried to fix the error but I
didn’t figure out what was happening so I decided to rollback the
changes.[/color][/color]
What is the exact, copied/pasted, error? Also, after you modified the
/etc/sysconfig file did you do anything else? Some of those files need to
have you run SuSEconfig (maybe that’s only in the old days) command to
have those variables get copied to the right places, e.g. to the actual
init scripts, or conf files, or service units (SLES 12+), which actually
do the work. /etc/sysconfig has a lot of great data, but it is made for
administration tools more than for actual applications managed by those
tools, so my concern is that the PostgreSQL startup code may not be
looking there, though I do not have a system to check right now.
Let’s just start with the exact error message. If you want to get a lot
of great output, prepend the init script with 'bash -xv ’ so we can see
all of the evaluation of variables, etc. in that script. It may help us
figure out if a variable is not changing properly.
–
Good luck.
If you find this post helpful and are logged into the web interface,
show your appreciation and click on the star below.
If you want to send me a private message, please let me know in the
forum as I do not use the web interface often.
Hi, bellow is what I have tried minutes ago (in red)
[QUOTE=ab;54497]On 09/20/2018 07:04 AM, mlino wrote:[color=blue]
[color=green]
I stopped the server the I did a copy for the new location using rsync
rsync -av -o -g /var/lib/pgsql/ /dados/pgsql[/color][/color]
rsync is awesome.
[color=blue][color=green]
Then I changed the location of the files in /etc/sysconfig/postgresql
pointing to the new location. I have left the new location commented as
you can see bellow.[/color][/color]
I presume before you rolled back you did not have the new location
commented out. Thanks for not just removing it; that was a good idea.
[color=blue][color=green]
POSTGRES_DATADIR=“~postgres/data”
#POSTGRES_DATADIR=“/dados/pgsql/data”After that, I tried to restart the server and it doesn’t start due to an
error in the postgresql-init script. Then I tried to fix the error but I
didn’t figure out what was happening so I decided to rollback the
changes.[/color][/color]
What is the exact, copied/pasted, error?
[color=red]
I don’t remember that. Was something related to the pg_ctl initdb…
[/color]
Also, after you modified the
/etc/sysconfig file did you do anything else? Some of those files need to
have you run SuSEconfig (maybe that’s only in the old days) command to
have those variables get copied to the right places, e.g. to the actual
init scripts, or conf files, or service units (SLES 12+), which actually
do the work. /etc/sysconfig has a lot of great data, but it is made for
administration tools more than for actual applications managed by those
tools, so my concern is that the PostgreSQL startup code may not be
looking there, though I do not have a system to check right now.
Let’s just start with the exact error message. If you want to get a lot
of great output, prepend the init script with 'bash -xv ’ so we can see
all of the evaluation of variables, etc. in that script. It may help us
figure out if a variable is not changing properly.
[color=red]
I stopped the server minutes ago and change the DATADIR to the following:
POSTGRES_DATADIR=“./dados/pgsql/data”
Then I restarted the server and it goes up and running. Even with server up and runnign I cannot connect due to the following error:
Unable to connect to server:could not connect to server: Connection refused (0x0000274D/10061).
Good luck.
If you find this post helpful and are logged into the web interface,
show your appreciation and click on the star below.
If you want to send me a private message, please let me know in the
forum as I do not use the web interface often.[/QUOTE]
Hi mlino,
while it may appear to be some sort of “chicken exit”, have you thought about symlinking instead of redefining per config? So that ~postgres/data is a symlink to /dados/pgsql/data and everything else stays the same?
Regards,
J