[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: bash or sql script to copy foreign table to locale table
From: khoff <koff10 () hotmail ! com>
Date: 2022-05-20 9:40:03
Message-ID: PAXP193MB1533ABC190D3F84DEE56FBAECAD39 () PAXP193MB1533 ! EURP193 ! PROD ! OUTLOOK ! COM
[Download RAW message or body]
Hi Ron,
It works
Thanks for your contribution
khoff.
Le 20/05/2022 à 05:17, Ron a écrit :
> Please do not hijack existing threads.
>
> On 5/19/22 18:55, koff wrote:
>> Hi all,
>>
>> I need little help please,
>>
>> I'v already foreign tables (from oracle_fdw) in my postgresql
>> environment.
>>
>> I'm Just looking for script shell or sql ( easy way) to migrate
>> this foreign tables to locale table .
>>
>> My script below has some issue
>>
>> thanks lot
>>
>> ------script migrate---
>>
>> #!/bin/bash
>> export PGDATA=/pg01/pgdata/9.6/data
>> export PGUSER="postgres"
>> export PGPORT="5433"
>> export PGPASSWORD="ttooe2"
>>
>> for tbl in `psql -qAt data_2020 -c "select foreign_table_name
>> from information_schema.foreign_tables;"`;
>> do
> ## note the added semicolon
>> psql data_2020 -c "create table public.\"$tbl\" as select * from
>> fdw_link.\"$tbl\";"
> Here, either psql COPY the fdw_link.\"$tbl\" data OUT to a local file,
> and then COPY IN to public.\"$tbl\" *OR* INSERT INTO public.\"$tbl\"
> SELECT * FROM fdw_link.\"$tbl\";
>
>> done
>>
>> ----end of script---
>>
>> the database is : data_2020
>>
>> foreign_table schema is : fdw_link
>>
>> and want to copy to public schema
>>
>>
>>
>>
>>
>>
>
> --
> Angular momentum makes the world go 'round.
[Attachment #3 (text/html)]
<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<p>Hi Ron,</p>
<p>It works <br>
</p>
<p>Thanks for your contribution <br>
</p>
<p>khoff.<br>
</p>
<div class="moz-cite-prefix">Le 20/05/2022 à 05:17, Ron a écrit :<br>
</div>
<blockquote type="cite" \
cite="mid:d3bc0de9-6058-4643-30af-6937453ac870@gmail.com">
Please do not hijack existing threads.<br>
<br>
<div class="moz-cite-prefix">On 5/19/22 18:55, koff wrote:<br>
</div>
<blockquote type="cite" \
cite="mid:PAXP193MB15339A6E149E3CB509AD2827CAD09@PAXP193MB1533.EURP193.PROD.OUTLOOK.COM">Hi
all, <br>
<br>
I need little help please, <br>
<br>
I'v already foreign tables (from oracle_fdw) in my
postgresql environment. <br>
<br>
I'm Just looking for script shell or sql ( easy \
way) to
migrate this foreign tables to locale table . \
<br> <br>
My script below has some issue <br>
<br>
thanks lot <br>
<br>
------script migrate--- <br>
<br>
#!/bin/bash <br>
export PGDATA=/pg01/pgdata/9.6/data <br>
export PGUSER="postgres" <br>
export PGPORT="5433" <br>
export PGPASSWORD="ttooe2" <br>
<br>
<font face="monospace">for tbl in `psql -qAt data_2020 -c
"select foreign_table_name from
information_schema.foreign_tables;"`; <br>
do</font><br>
</blockquote>
<font face="monospace"> \
## note the added semicolon</font><br>
<blockquote type="cite" \
cite="mid:PAXP193MB15339A6E149E3CB509AD2827CAD09@PAXP193MB1533.EURP193.PROD.OUTLOOK.COM"><font \
face="monospace"> psql data_2020 -c \
"create table
public.\"$tbl\" as select * from \
fdw_link.\"$tbl\";"</font></blockquote>
Here, either psql COPY the <font \
face="monospace">fdw_link.\"$tbl\"</font> data OUT to a local file, and \
then COPY IN to <font face="monospace">public.\"$tbl\"</font> <b>OR</b> \
INSERT INTO <font face="monospace">public.\"$tbl\"</font> SELECT * FROM \
<font face="monospace">fdw_link.\"$tbl\";</font><br> <br>
<blockquote type="cite" \
cite="mid:PAXP193MB15339A6E149E3CB509AD2827CAD09@PAXP193MB1533.EURP193.PROD.OUTLOOK.COM"><font \
face="monospace">done <br> </font><br>
----end of script--- <br>
<br>
the database is : data_2020 <br>
<br>
foreign_table schema is : fdw_link <br>
<br>
and want to copy to public schema <br>
<br>
<br>
<br>
<br>
<br>
<br>
</blockquote>
<br>
<div class="moz-signature">-- <br>
Angular momentum makes the world go 'round.</div>
</blockquote>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic