From: | Ian Barwick <ian(dot)barwick(at)2ndquadrant(dot)com> |
---|---|
To: | Alex Williams <valenceshell(at)protonmail(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Restoring a database restores to unexpected tablespace |
Date: | 2019-07-10 02:35:15 |
Message-ID: | 7fd8069f-a236-4e79-477c-7313ee416407@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 7/10/19 2:56 AM, Alex Williams wrote:
> Hi,
>
> Can someone point me in the right direction for this issue we are having -- our goal is to dump a database that is currently on a tablespace named data2 that we want to restore on the same server but on tablespace pg_default -- we tried other ways like:
> ALTER DATABASE "[database_name]" SET default_tablespace = [new_tablespace];
> alter table all in tablespace data2 set tablespace pg_default;
>
> But we want to try it with a pgdump/psql.
>
> To reproduce on our end (Server 9.5):
>
> 1. create new database for the restore with the tablespace as pg_default
>
> 2. Dump the source database (currently on data2 tablespace) with the following command:
> sudo -u postgres pg_dump mydatabase --no-owner --no-tablespaces | gzip > mydatabase.gz
>
> 3. Restore the database with this command:
> zcat /var/backup/db/mydatabase.gz | sudo -H -u postgres psql --quiet -e -c 'SET default_tablespace = pg_default;' -f - mydatabase_test > /tmp/mydatabase_test.log
>
> What happens during the restore is that all tables are created on data2, not pg_default.
>
> Any help would be greatly appreciated.
This should work.
Double-check each step to make sure nothing has been missed out somewhere, e.g.
in step 2 you create mydatabase.gz in the current working directory but in step 3 restore it
from an absolute filepath, which is a common cause of errors.
Also maybe try dumping an individual table definition (pg_dump --schema-only --table=sometablename ...) and check
exactly what's being dumped and how it gets restored.
Regards
Ian Barwick
--
Ian Barwick https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2019-07-10 04:04:27 | Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR |
Previous Message | Julie Nishimura | 2019-07-10 02:10:56 | Re: number of concurrent writes that are allowed for database |