Linux多PostgreSQL安装环境下添加新用户名及切换对应实例用户方法

Alright, let's break down your questions one by one:

First off, let's clarify a key point: the su postgres command switches you to the system-level postgres user, not a user tied to a specific PostgreSQL instance. By default, all PostgreSQL instances on your system share this single system user—you don't get separate system users for each version/port.

That means there's no -p option for su to target a specific PostgreSQL instance. Instead, once you're logged into the system postgres user, you can connect to your 9.4 instance (port 5433) using psql with the -p flag:

su postgres
psql -p 5433

Alternatively, you can connect directly from your regular user account (if your pg_hba.conf allows it) without switching first:

psql -U postgres -p 5433

Wait a second—usually, when you initialize a PostgreSQL database cluster (the storage for an instance), the postgres superuser is created automatically. If you don't have it, here's what to do:

Case 1: You haven't initialized the 9.4 cluster yet #

First, initialize the database cluster for 9.4 using the version-specific initdb command. The exact path depends on your installation, but it might look like this:

# Switch to the system postgres user first
su postgres
# Run 9.4's initdb (adjust paths to match your setup)
/usr/pgsql-9.4/bin/initdb -D /var/lib/pgsql/9.4/data

After initialization, start the 9.4 service:

# For systemd-based systems (CentOS 7+, Ubuntu 16.04+)
systemctl start postgresql-9.4
# For older sysvinit systems
service postgresql-9.4 start

The postgres user will be present in the 9.4 instance by default after this.

Case 2: The postgres user was accidentally deleted from the 9.4 instance #

If you already initialized the cluster but lost the postgres user, you can recreate it by connecting to the template1 database (the default template for new databases) with a superuser-level connection:

1. Switch to the system postgres user: su postgres
2. Connect to the 9.4 instance's template1 database:

   psql -p 5433 template1
   

3. Inside the psql shell, run these commands to recreate the superuser:

   CREATE USER postgres WITH SUPERUSER CREATEDB CREATEROLE LOGIN;
   -- Optional: Set a password if needed
   ALTER USER postgres PASSWORD 'your_secure_password';
   

4. Exit psql with \q.

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