Copying PostgreSQL Server to Another Server

Question

I'm looking to copy a PostgreSQL Server to Another server.

1. I want to migrate entire server which might contain multiple databases
2. I am trying to do it with cloud databases (e.g. AWS which doesn't provide super user privileges for RDS) so it would be better to provide a solution with Users of attribute Create role, Create DB Password valid until infinity

I want something like (This query doesn't exists just an example)

pg_dumpall -C -h localhost -U localuser | psql -h remotehost -U remoteuser

I know that below query exists but it is does not work same for pg_dumpall

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

Answer 1

pg_dumpall -c -h localhost -U localuser | psql -h remotehost -U remoteuser

-c for clean databases before recreating. also you should have superuser privilege for this user.

Answer 2

If your goal is to move all databases from cluster A to cluster B, except for one specific database on cluster A, you can add the --exclude-database=pattern parameter to pg_dumpall:

--exclude-database=pattern

Do not dump databases whose name matches pattern. Multiple patterns can be excluded by writing multiple --exclude-database switches. The pattern parameter is interpreted as a pattern according to the same rules used by psql's \d commands (see Patterns), so multiple databases can also be excluded by writing wildcard characters in the pattern. When using wildcards, be careful to quote the pattern if needed to prevent shell wildcard expansion.

pg_dumpall -h localhost -U localuser --exclude-database=the_db_you_do_not_want \ 
| psql -h remotehost -U remoteuser

As mentioned in the comments, -C doesn't exist for this command because it's unnecessary. The --clean is optional and not really advised as a default - if you misdirect your command and target the wrong cluster, it's better for it to warn you than to quietly wipe it clean.

---

Your edits fundamentally change the question and the latest comment doesn't really align with the latest revision - multi-db dump&restore is what you initially asked about, and that's exactly what pg_dumpall is meant to do.

I suspect your problem isn't really about PostgreSQL psql, pg_dump or pg_dumpall, it's about how these things are done on AWS RDS. Here's the official manual.