PostgreSQL：能否从当前连接库修改未连接数据库的Schema？

Short answer: No, you can't directly alter a schema's owner in a different database while staying connected to your current one—and here's why, plus how to automate this properly.

Why your attempts didn't work #

PostgreSQL's ALTER SCHEMA command is strictly scoped to the database you're currently connected to. Each database maintains its own independent set of system tables that track schema metadata, so there's no built-in syntax to cross into another database's namespace directly:

• ALTER DATABASE ... ALTER SCHEMA isn't valid PostgreSQL syntax—there's no nested command structure for modifying schemas across databases.
• ALTER SCHEMA "db_name".name ... fails because schema names only have meaning within the context of your currently connected database; you can't prefix a schema with a database name to target it.

How to automate schema owner changes across databases #

Since direct cross-database modification isn't possible, you'll need to target each database individually. Here are two reliable ways to automate this workflow:

1. Batch script with psql

The simplest approach is to write a shell script that connects to each target database one by one and runs the ALTER SCHEMA command. This works seamlessly for most automation pipelines:

#!/bin/bash
# Configure your parameters
DB_USER="your_postgres_user"
TARGET_SCHEMA="your_schema_name"
NEW_OWNER="new_owner_user"
# List of databases to update
DATABASES=("db1" "db2" "db3" "db4")

# Loop through each database and execute the command
for db in "${DATABASES[@]}"
do
  echo "Updating schema $TARGET_SCHEMA in database $db..."
  psql -U "$DB_USER" -d "$db" -c "ALTER SCHEMA $TARGET_SCHEMA OWNER TO $NEW_OWNER;"
done

Make sure your PostgreSQL user has the necessary privileges (like ALTER on the target schema and access to each database) and set up smooth authentication (use ~/.pgpass to avoid manual password entry).

2. Use the dblink extension (for in-database automation)

If you need to run the operation from within a PostgreSQL session (instead of a shell script), you can use the dblink extension to create a remote connection to the target database and execute the command:

-- First, ensure dblink is installed in your current database
CREATE EXTENSION IF NOT EXISTS dblink;

-- Connect to the target database and alter the schema
SELECT dblink_connect('target_db_connection', 'dbname=target_db user=your_user');
SELECT dblink('target_db_connection', 'ALTER SCHEMA your_schema OWNER TO new_owner');
SELECT dblink_disconnect('target_db_connection');

Note: This requires your user has access to both the current and target databases, and dblink is enabled. For automation, you could wrap this in a function or pair it with pg_cron for scheduled jobs, though the shell script approach is generally more straightforward for bulk operations.

内容的提问来源于stack exchange，提问作者helvete