PostgreSQL – Creating Foreign Server Only If Not Exists

postgresql

As far as I know in order to create a foreign server (for a FDW) you need to do the following:

CREATE SERVER my_wrapper FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    dbname 'wrapperDB',
    host '127.0.0.1',
    port '5432'
);")

But because I create a migration script for an existing database, I want to be able to check whether this server exists with the same name and then create in a similar approach that is used upon tables:

CREATE TABLE IF NOT EXISTS mytable ...

Or in stored procedures:

CREATE OR REPLACE FUNCTION public.myfunction(text)....

Do you have any idea how to do that?

Best Answer

Try a DO statement:

DO
$$BEGIN
   CREATE SERVER ...;
EXCEPTION
   WHEN duplicate_object THEN
      NULL;  -- ignore
END;$$;

Related Solutions

PostgreSQL 9.3 – Streaming Replication Using Two Servers

You seem to be trying to replicate from one server to another that wasn't set up using a copy of the original server. That's why:

database system identifier differs between the primary and standby. The primary's identifier is 6022019027749040119, the standby's identifier is 6033562405193904122.

Because each newly initdb'd PostgreSQL gets a new random system identifier. When you copy an existing PostgreSQL install, it keeps the same system identifier. That's how PostgreSQL can keep track of whether one server can replay WAL from another.

You can only use physical replication if the replica is a copy (file-system level backup e.g. pg_basebackup) of the master. See the manual's detailed coverage on replication for more information.

Update:

The instructions shown above should be fine, but they're not as clear as they could be.

The standby server's data directory is supposed to be replaced by the base backup you create at step 8, if it exists in the first place.

You can't make an existing PostgreSQL instance into a standby for another without replacing its data directory. You need a copy of the master's data directory to run a standby. A common way to set that up is to take an existing standby, delete its data directory, replace it with a copy of the master's data directory, and then configure it as a replication slave. That's what I think step 8 is supposed to be doing.

Instead of doing that I think you probably used an existing data directory for the slave and tried to start it up as a replica of the master. That will not work, and will result in the errors you showed.

The main PostgreSQL documentation on replication is the recommended and primary resource for information. I suggest going there first.

You might also want to check out repmgr, which helps automate replication and failover tasks.

Postgres pg_basebackup – Resolving ‘Directory Exists but is Not Empty’ Error

In my opinion it is not related to mklink or Windows issues.

Per pg_basebackup docs, the main data directory will be placed in the target directory, but all other tablespaces will be placed in the same absolute path as they have on the server.

-F format
--format=format

    Selects the format for the output. format can be one of the following:

    p
    plain

    Write the output as plain files, with the same layout as the current data directory and tablespaces. When the cluster has no
    additional tablespaces, the whole database will be placed in the
    target directory. If the cluster contains additional tablespaces, the
    main data directory will be placed in the target directory, but all
    other tablespaces will be placed in the same absolute path as they
    have on the server.

    This is the default format.

Solution: do not run it on the same server, or change backup format.

Best Answer

Related Solutions

PostgreSQL 9.3 – Streaming Replication Using Two Servers

Postgres pg_basebackup – Resolving ‘Directory Exists but is Not Empty’ Error

Related Question