pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedimport tablespaces from oracle to postgres
5+ messages / 2 participants
[nested] [flat]
* import tablespaces from oracle to postgres
@ 2019-01-29 14:07 Pepe TD Vo <pepevo@yahoo.com>
2019-01-29 14:36 ` Re: import tablespaces from oracle to postgres Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Pepe TD Vo @ 2019-01-29 14:07 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi experts,
I am using Ora2pg to export cidr in oracle to postgres. When the export Tablespace.sql is done, I import into the Postgres, Cidr. I get ERROR below
$psql -U posgres -d CIDR < TABLESPACE_cidrdba.sql
SET
CREATE TABLESPACE
ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use as a tablespace.
even trying to create tablespace manually still said it's already in use, but PG_11_20109051 is belong to cidr_data tablespace, how can I create new tablespaces for cidr postgres db?
$psql -U posgres -d CIDR $psql> create tablespace cidr_lg location 'opt/postgres11/data/cidr';
SET
CREATE TABLESPACE
ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use as a tablespace.
$psql> create tablespace cidr_dg location 'opt/postgres11/data/cidr';SET
CREATE TABLESPACE
ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use as a tablespace.
how can I create new tablespaces for cidr postgres db?
v/r,
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: import tablespaces from oracle to postgres
2019-01-29 14:07 import tablespaces from oracle to postgres Pepe TD Vo <pepevo@yahoo.com>
@ 2019-01-29 14:36 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-01-29 14:44 ` Re: import tablespaces from oracle to postgres Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Shreeyansh Dba @ 2019-01-29 14:36 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi Pepe,
Yes, you can create the tablespaces manually just by changing your
tablespace name along with the location.
Also, you can use the existing tablespace at the time of importing into the
Postgres.
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Tue, Jan 29, 2019 at 7:37 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
> Hi experts,
>
> I am using Ora2pg to export cidr in oracle to postgres. When the export
> Tablespace.sql is done, I import into the Postgres, Cidr. I get ERROR below
>
> $psql -U posgres -d CIDR < TABLESPACE_cidrdba.sql
> SET
> CREATE TABLESPACE
> ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use
> as a tablespace.
>
> even trying to create tablespace manually still said it's already in use,
> but PG_11_20109051 is belong to cidr_data tablespace, how can I create new
> tablespaces for cidr postgres db?
>
>
> $psql -U posgres -d CIDR
> $psql> create tablespace cidr_lg location 'opt/postgres11/data/cidr';
> SET
> CREATE TABLESPACE
> ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use
> as a tablespace.
>
> $psql> create tablespace cidr_dg location 'opt/postgres11/data/cidr';SET
> CREATE TABLESPACE
> ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use
> as a tablespace.
>
> how can I create new tablespaces for cidr postgres db?
> v/r,
>
>
>
> *Bach-Nga*No one in this world is pure and perfect. If you avoid people
> for their mistakes you will be alone. So judge less, love and forgive more.
> To call him a dog hardly seems to do him justice though in as much as he
> had four legs, a tail, and barked, I admit he was, to all outward
> appearances. But to those who knew him well, he was a perfect gentleman
> (Hermione Gingold)
>
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: import tablespaces from oracle to postgres
2019-01-29 14:07 import tablespaces from oracle to postgres Pepe TD Vo <pepevo@yahoo.com>
2019-01-29 14:36 ` Re: import tablespaces from oracle to postgres Shreeyansh Dba <shreeyansh2014@gmail.com>
@ 2019-01-29 14:44 ` Pepe TD Vo <pepevo@yahoo.com>
2019-01-29 15:30 ` Re: import tablespaces from oracle to postgres Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-01-29 15:32 ` Re: import tablespaces from oracle to postgres Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 2 replies; 5+ messages in thread
From: Pepe TD Vo @ 2019-01-29 14:44 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Yes, sir/mam'm,
I manually created those extra tablespaces and still get error above. Seems to me one tablespace for each directory? I can't have multiple tablespaces under /opt/postgres11/data/cidr right? currently the cidr datbase default tablespace is cidr_data. How can I create database with default_tablespaces with cidr_dba, cidr_lg, cidr_dg, etc...? can a database has multi-tablespaces?
thank you,
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, January 29, 2019 9:37 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Pepe,
Yes, you can create the tablespaces manually just by changing your tablespace name along with the location.
Also, you can use the existing tablespace at the time of importing into the Postgres.
Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com
On Tue, Jan 29, 2019 at 7:37 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
Hi experts,
I am using Ora2pg to export cidr in oracle to postgres. When the export Tablespace.sql is done, I import into the Postgres, Cidr. I get ERROR below
$psql -U posgres -d CIDR < TABLESPACE_cidrdba.sql
SET
CREATE TABLESPACE
ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use as a tablespace.
even trying to create tablespace manually still said it's already in use, but PG_11_20109051 is belong to cidr_data tablespace, how can I create new tablespaces for cidr postgres db?
$psql -U posgres -d CIDR $psql> create tablespace cidr_lg location 'opt/postgres11/data/cidr';
SET
CREATE TABLESPACE
ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use as a tablespace.
$psql> create tablespace cidr_dg location 'opt/postgres11/data/cidr';SET
CREATE TABLESPACE
ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use as a tablespace.
how can I create new tablespaces for cidr postgres db?
v/r,
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: import tablespaces from oracle to postgres
2019-01-29 14:07 import tablespaces from oracle to postgres Pepe TD Vo <pepevo@yahoo.com>
2019-01-29 14:36 ` Re: import tablespaces from oracle to postgres Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-01-29 14:44 ` Re: import tablespaces from oracle to postgres Pepe TD Vo <pepevo@yahoo.com>
@ 2019-01-29 15:30 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Shreeyansh Dba @ 2019-01-29 15:30 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Yes. you can create one tablespace in one directory location and if you
create a database with default_tablespaces with cidr_dba, cidr_lg, cidr_dg,
etc you need to a create directory for each thses tablespaces respectively.
Also, you can't a create multi-tablespaces in one database. However, you
can create multi-tablespaces in one cluster.
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Tue, Jan 29, 2019 at 8:14 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
> Yes, sir/mam'm,
>
> I manually created those extra tablespaces and still get error above.
> Seems to me one tablespace for each directory? I can't have multiple
> tablespaces under /opt/postgres11/data/cidr right? currently the cidr
> datbase default tablespace is cidr_data. How can I create database with
> default_tablespaces with cidr_dba, cidr_lg, cidr_dg, etc...? can a
> database has multi-tablespaces?
>
> thank you,
>
>
>
> *Bach-Nga*No one in this world is pure and perfect. If you avoid people
> for their mistakes you will be alone. So judge less, love and forgive more.
> To call him a dog hardly seems to do him justice though in as much as he
> had four legs, a tail, and barked, I admit he was, to all outward
> appearances. But to those who knew him well, he was a perfect gentleman
> (Hermione Gingold)
>
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
>
>
> On Tuesday, January 29, 2019 9:37 AM, Shreeyansh Dba <
> shreeyansh2014@gmail.com> wrote:
>
>
> Hi Pepe,
>
> Yes, you can create the tablespaces manually just by changing your
> tablespace name along with the location.
>
> Also, you can use the existing tablespace at the time of importing into
> the Postgres.
>
> Thanks & Regards,
> *Shreeyansh DBA Team*
> www.shreeyansh.com
>
>
> On Tue, Jan 29, 2019 at 7:37 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> Hi experts,
>
> I am using Ora2pg to export cidr in oracle to postgres. When the export
> Tablespace.sql is done, I import into the Postgres, Cidr. I get ERROR below
>
> $psql -U posgres -d CIDR < TABLESPACE_cidrdba.sql
> SET
> CREATE TABLESPACE
> ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use
> as a tablespace.
>
> even trying to create tablespace manually still said it's already in use,
> but PG_11_20109051 is belong to cidr_data tablespace, how can I create new
> tablespaces for cidr postgres db?
>
>
> $psql -U posgres -d CIDR
> $psql> create tablespace cidr_lg location 'opt/postgres11/data/cidr';
> SET
> CREATE TABLESPACE
> ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use
> as a tablespace.
>
> $psql> create tablespace cidr_dg location 'opt/postgres11/data/cidr';SET
> CREATE TABLESPACE
> ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use
> as a tablespace.
>
> how can I create new tablespaces for cidr postgres db?
> v/r,
>
>
>
> *Bach-Nga*No one in this world is pure and perfect. If you avoid people
> for their mistakes you will be alone. So judge less, love and forgive more.
> To call him a dog hardly seems to do him justice though in as much as he
> had four legs, a tail, and barked, I admit he was, to all outward
> appearances. But to those who knew him well, he was a perfect gentleman
> (Hermione Gingold)
>
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: import tablespaces from oracle to postgres
2019-01-29 14:07 import tablespaces from oracle to postgres Pepe TD Vo <pepevo@yahoo.com>
2019-01-29 14:36 ` Re: import tablespaces from oracle to postgres Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-01-29 14:44 ` Re: import tablespaces from oracle to postgres Pepe TD Vo <pepevo@yahoo.com>
@ 2019-01-29 15:32 ` Pepe TD Vo <pepevo@yahoo.com>
1 sibling, 0 replies; 5+ messages in thread
From: Pepe TD Vo @ 2019-01-29 15:32 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
seem to me, each datafile doesn't want to share the data_directory folder.
I tried to drop and recreate the tablespace success but it's not take the 2nd one
psql# drop tablespace "cidr_data_lg";DROP TABLESPACEpsql# CREATE TABLESPACE cidr_data_lg LOCATION '/opt/postgres11/data/cidr';CREATE TABLESPACEpsql# CREATE TABLESPACE cidr_data_md LOCATION '/opt/postgres11/data/cidr';
ERROR: directory "/opt/postgres11/data/cidr/PG_11_201809051" already in use as a tablespacepsql# CREATE TABLESPACE cidr_data_dg LOCATION '/opt/postgres11/data/cidr';
ERROR: directory "/opt/postgres11/data/cidr/PG_11_201809051" already in use as a tablespace
under /opt/postgres11/data/cidr folder, I see PG_11_201809051 folder and there's a datafile there.
aren't they supposed to share the folder /opt/postgres11/data/cidr?
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, January 29, 2019 9:44 AM, Pepe TD Vo <pepevo@yahoo.com> wrote:
Yes, sir/mam'm,
I manually created those extra tablespaces and still get error above. Seems to me one tablespace for each directory? I can't have multiple tablespaces under /opt/postgres11/data/cidr right? currently the cidr datbase default tablespace is cidr_data. How can I create database with default_tablespaces with cidr_dba, cidr_lg, cidr_dg, etc...? can a database has multi-tablespaces?
thank you,
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, January 29, 2019 9:37 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Pepe,
Yes, you can create the tablespaces manually just by changing your tablespace name along with the location.
Also, you can use the existing tablespace at the time of importing into the Postgres.
Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com
On Tue, Jan 29, 2019 at 7:37 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
Hi experts,
I am using Ora2pg to export cidr in oracle to postgres. When the export Tablespace.sql is done, I import into the Postgres, Cidr. I get ERROR below
$psql -U posgres -d CIDR < TABLESPACE_cidrdba.sql
SET
CREATE TABLESPACE
ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use as a tablespace.
even trying to create tablespace manually still said it's already in use, but PG_11_20109051 is belong to cidr_data tablespace, how can I create new tablespaces for cidr postgres db?
$psql -U posgres -d CIDR $psql> create tablespace cidr_lg location 'opt/postgres11/data/cidr';
SET
CREATE TABLESPACE
ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use as a tablespace.
$psql> create tablespace cidr_dg location 'opt/postgres11/data/cidr';SET
CREATE TABLESPACE
ERROR: directory"/opt/postgres11/data/cidr/PG_11_201809051" already in use as a tablespace.
how can I create new tablespaces for cidr postgres db?
v/r,
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-01-29 15:32 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-29 14:07 import tablespaces from oracle to postgres Pepe TD Vo <pepevo@yahoo.com>
2019-01-29 14:36 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-01-29 14:44 ` Pepe TD Vo <pepevo@yahoo.com>
2019-01-29 15:30 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-01-29 15:32 ` Pepe TD Vo <pepevo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox