<div dir="ltr">You can try using this extension available on github for schema cloning, it provides complete schema clone (DDL and Data).<div><br></div><div><a href="https://github.com/vibhorkum/cloneschema">https://github.com/vibhorkum/cloneschema<br></a></div><div><br></div><div>-- Ahsan</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Jul 31, 2017 at 2:09 PM, Regina Obe <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div lang="EN-US" link="#0563C1" vlink="#954F72"><div class="m_-7680828817615981855WordSection1"><p class="MsoNormal"><span style="color:#1f497d">I think you'd have better luck asking this on pgsql-general news list. <a href="https://www.postgresql.org/list/pgsql-general/" target="_blank">https://www.postgresql.org/<wbr>list/pgsql-general/</a><u></u><u></u></span></p><p class="MsoNormal"><span style="color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="color:#1f497d">There is no PostGIS related question here.<u></u><u></u></span></p><p class="MsoNormal"><span style="color:#1f497d"><u></u> <u></u></span></p><div><div style="border:none;border-top:solid #e1e1e1 1.0pt;padding:3.0pt 0in 0in 0in"><p class="MsoNormal" style="margin-left:.5in"><b>From:</b> postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@<wbr>lists.osgeo.org</a>] <b>On Behalf Of </b>Olivier Leprêtre<br><b>Sent:</b> Sunday, July 30, 2017 2:24 PM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><b>Subject:</b> [postgis-users] clone schema - pg_get_serial_sequence returns null<u></u><u></u></p></div></div><div><div class="h5"><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p><p class="MsoNormal" style="margin-left:.5in"><span lang="FR" style="font-size:9.0pt;font-family:"Verdana",sans-serif">Hi,<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="FR" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">I want to clone a schema, so I wrote the script below. My problem is that I can’t get sequences with pg_get_serial_sequence for the cloned primary keys.<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">The sequence seems to be ok in the cloned schema :<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> idapp integer NOT NULL DEFAULT nextval('pr_1_1.ptbt_idapp_<wbr>seq'::regclass),<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> CONSTRAINT adn_ptbt_pkey PRIMARY KEY (idapp)<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">But if I run select pg_get_serial_sequence('pr_1_<wbr>1.ptbt', 'idapp') it returns <NULL> <u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">Conversely, running pg_get_serial_sequence('<wbr>source.ptbt', 'idapp') (from the source schema) returns the correct sequence.<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB">I’m using 9.6, I saw that it could be a problem because column is not directly as “serial”, is this an answer without solution ?<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">Any help will be appreciated !<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">Thanks,<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">Olivier<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">CREATE OR REPLACE FUNCTION params.prm_colne_schema(<wbr>source_schema text, dest_schema text) RETURNS boolean AS<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">$BODY$<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">DECLARE <u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> object text;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> buffer text;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> default_ text;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> column_ text;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> sqls text;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">BEGIN<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> IF dest_schema <> 'public' THEN<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> EXECUTE 'DROP SCHEMA if exists ' || dest_schema || ' CASCADE';<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> EXECUTE 'CREATE SCHEMA ' || dest_schema;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> END IF; <u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> -- copy sequences<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> FOR object IN<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> LOOP<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> END LOOP;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> -- copy tables without constraints<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> FOR object IN<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema and substr(table_name,1,4) <> 'src_' <u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> LOOP <u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> buffer := dest_schema || '.' || object;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING DEFAULTS)';<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> FOR column_, default_ IN<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> <wbr> SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME = object<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> LOOP<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> <wbr> EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ ||' DROP NOT NULL;';<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> END LOOP;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> END LOOP;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> -- copy tables with constraints<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> FOR object IN<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> <wbr> SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema and substr(table_name,1,4) = 'src_' <u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> LOOP <u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> buffer := dest_schema || '.' || object;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING ALL)';<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> -- copy sequences<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> FOR column_, default_ IN<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> <wbr> SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME = object AND column_default LIKE 'nextval(%' || source_schema || '%::regclass)'<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> LOOP<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> <wbr> sqls='ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> <wbr> RAISE NOTICE 'default=%',sqls;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> <wbr> EXECUTE (sqls);<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> END LOOP;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> END LOOP;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> RETURN true;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">EXCEPTION<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> WHEN others THEN<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif"> RETURN false;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">END;<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">$BODY$<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><span lang="EN-GB" style="font-size:9.0pt;font-family:"Verdana",sans-serif">LANGUAGE plpgsql VOLATILE;<u></u><u></u></span></p></div></div></div></div><br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature" data-smartmail="gmail_signature">Ahsan Hadi<br>Snr Director Product Development<br>EnterpriseDB Corporation<br>The Enterprise Postgres Company<br><br>Phone: +92-51-8358874 <br>Mobile: +92-333-5162114<br><br>Website: <a href="http://www.enterprisedb.com" target="_blank">www.enterprisedb.com</a><br>EnterpriseDB Blog: <a href="http://blogs.enterprisedb.com/" target="_blank">http://blogs.enterprisedb.com/</a><br>Follow us on Twitter: <a href="http://www.twitter.com/enterprisedb" target="_blank">http://www.twitter.com/enterprisedb</a><br><br>This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.</div>
</div>