How to run a query in 50 databases in postgres?

Question

I have 50 databases. All the table schema in all the databases are the same. If I have a new column to be added to one table, I will have to run 50 times to update in all databases. This is very difficult all the time.

Is there an editor for postgres where there is a way to execute a query in all databases?

Currently, I am using dbeaver. I have to change the connection all the time and execute the query. Please help.

Example: I had to alter a table by adding a column. ALTER TABLE table-name ADD new-column-name column-definition;

It is very difficult to execute for 50 databases.

You might consider using a database schema migration tool such as Flyway or Liquibase. — Basil Bourque
– Basil Bourque, Commented Jan 18, 2020 at 1:10
One approach is to use scripts to automate the process, such as a bash script or batch file to make each connection in turn and then use psql to run the ALTER TABLE script. — rd_nielsen
– rd_nielsen, Commented Jan 18, 2020 at 2:18

Bharti Mohane · Accepted Answer · 2020-01-18 05:57:10Z

2

For executing this answer, you need to have dblink extension.

DO $$
DECLARE
i text;
BEGIN

FOR i in SELECT datname FROM pg_database
LOOP
 EXECUTE 'select * from dblink(''host=localhost
     user=postgres
     password=postgres
     dbname='||i||' port=5432'',''ALTER TABLE table-name ADD new-column-name column-definition'' ) tt(
     updated text)'
END LOOP;
END $$

answered Jan 18, 2020 at 5:57

Bharti Mohane

7067 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tkr karthikeyan Over a year ago

Should dblink be created already? Please clarify. @Bharti Mohane

Bharti Mohane Over a year ago

you can create extension dblink in postgres, you can try this.

Collectives™ on Stack Overflow

How to run a query in 50 databases in postgres?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related