In alle Tables aller Datenbanken Primärkeys (auto-increment) einfügen (PostgreSQL v 9.6)

Hi!

Ich bin PostgreSQL Anfänger. Also bitte basht mich nicht zu hart für meine Frage…

Ich habe einen PostgreSQL 9.6 Server (EC2 auf AWS) laufen, der zu GCP (Google) migriert werden soll. Dafür müssen alle Tables einen Primärkey haben (was aktuell aber nicht der Fall ist).

Nun bin ich dabei, ein Skript zu schreiben, welches alle Datenbanken durchgeht und alle Tables auf Primärkeys untersucht und diesen dann als auto-increment einfügt.

Mit folgendem Snippet lasse ich mir die Tabellen ohne Primärkey ausgeben:

select tab.table_schema,
       tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco 
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name 
          and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
      and tab.table_schema not in ('pg_catalog', 'information_schema', 'pglogical')
      and tco.constraint_name is null
order by table_schema,
         table_name;

Und mit dieser Query füge in den Primärschlüssel ein:

ALTER TABLE mein_schema.meine_tabelle ADD COLUMN primary_key SERIAL PRIMARY KEY;

Nun ist die Fragen:

  1. Wir sieht das Skript aus, welches über alle Schemas interiert und dann in jeder Tabelle den Key einfügt?
  2. Wie mache ich, wenn ich das SQL Skript habe, dasselbe für alle Datenbanken des Servers (geht das auch als (ein) SQL Query oder muss ich für alle Datenbanken einzelne SQL Skripte schreiben?)

Gruß, vielen Dank für etwaige Hilfe und einen schönen Sonntag!

Christoph

Du kannst Dein SQL so erweitern, daß du fertige SQL-Kommandos erzeugst:

postgres=# select 'alter table ' || quote_ident(tab.table_schema) || '.' ||
       quote_ident(tab.table_name) || ' add column '
from information_schema.tables tab
left join information_schema.table_constraints tco 
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name 
          and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
      and tab.table_schema not in ('pg_catalog', 'information_schema', 'pglogical')
      and tco.constraint_name is null
order by 1 
;
                   ?column?                    
-----------------------------------------------
 alter table public."aufträge" add column 
 alter table public.beanstandungen add column 
 alter table public.ridgero add column 
 alter table public.warenbuchung add column 
 alter table public.zzeezz add column 
(5 rows)

Das passend erweitern, die Syntax kennst Du ja. Das kannst Du dann nutzen, um z.B. eine SQL-Datei zu erzeugen, oder du arbeitest mit gexec, siehe auch z.B. hier

Das wirst Du für alle Datenbanken separat ausführen müssen, weil Du nicht (so einfach) über Datenbanken hinweg etwas ausführen kannst.

Plane etwas Zeit ein, und mache das wenn keine auf die DB’s zugreift, weil die Tabellen gesperrt werden müssen.

Danke ich werden den Tipp testen!

Das ist genau die Krux an der Sache… Ich muss die Downtime Zeit minimieren. Deswegen versuche ich vorab ein Skript zu erstellen, welches dann alles schnell umsetzt.

Ich bin jetzt soweit, dass ich die SQL Befehle erstellen lassen kann (Verzeihung für die Formatierung… hab es jetzt einfach gecopypasted):

psql -c "select 'alter table ' || quote_ident(tab.table_schema) || '.' || quote_ident(tab.table_name) || ' add column primary_key SERIAL PRIMARY KEY; ' from information_schema.tables tab left join information_schema.table_constraints tco on tab.table_schema = tco.table_schema and tab.table_name = tco.table_name and tco.constraint_type = 'PRIMARY KEY' where tab.table_type = 'BASE TABLE' and tab.table_schema not in ('pg_catalog', 'information_schema', 'pglogical') and tco.constraint_name is null order by 1;" my_db

Das gexec sieht gut aus, ich würde es auch gern noch in dem Kommando integrieren. Das hier klappt leider nicht:

psql -c "select 'alter table ' || quote_ident(tab.table_schema) || '.' || quote_ident(tab.table_name) || ' add column primary_key SERIAL PRIMARY KEY; ' from information_schema.tables tab left join information_schema.table_constraints tco on tab.table_schema = tco.table_schema and tab.table_name = tco.table_name and tco.constraint_type = 'PRIMARY KEY' where tab.table_type = 'BASE TABLE' and tab.table_schema not in ('pg_catalog', 'information_schema', 'pglogical') and tco.constraint_name is null order by 1;\\gexec" my_db

Ich vermute ich muss das gexec besser Escapen. Hast du einen Tipp für mich.

Ansonsten sieht das schon ganz gut aus so. Die Datenbanken kann ich ja dann einfach Zeilenweise abfrühstücken…

Gruß und danke

UPDATE:

Mit

echo -e "select 'alter table ' || quote_ident(tab.table_schema) || '.' || quote_ident(tab.table_name) || ' add column primary_key SERIAL PRIMARY KEY; ' from information_schema.tables tab left join information_schema.table_constraints tco on tab.table_schema = tco.table_schema and tab.table_name = tco.table_name and tco.constraint_type = 'PRIMARY KEY' where tab.table_type = 'BASE TABLE' and tab.table_schema not in ('pg_catalog', 'information_schema', 'pglogical') and tco.constraint_name is null order by 1;\n\\gexec" | psql my_db

Hat es jetzt geklappt…
Gruß