When working with PostgreSQL on Ubuntu, it's common to find multiple directories that might appear to be duplicate installations. Here's what each standard directory typically contains:

/etc/postgresql/     # Configuration files
/usr/lib/postgresql/ # Binary files and shared libraries
/opt/postgresql/     # Optional or manually installed versions

First, let's verify which PostgreSQL services are actually running:

sudo systemctl list-units --type=service | grep postgres
sudo ps aux | grep postgres
pg_lsclusters    # Shows all PostgreSQL clusters on the system

To determine which installation your system is actually using:

which psql
pg_config --bindir
dpkg -l | grep postgresql

If you confirm there are indeed duplicate installations:

# For apt-installed versions:
sudo apt purge postgresql-XX
# Where XX is the version number

# For manually installed versions in /opt:
sudo rm -rf /opt/postgresql/
# Be extremely careful with this command

To ensure your Rails application connects to the correct PostgreSQL server:

# In database.yml
production:
  adapter: postgresql
  host: localhost
  port: 5432
  database: your_db
  username: your_user
  password: your_password

# Test connection from Rails console:
ActiveRecord::Base.connection.execute("SHOW server_version;")

Remember these directories serve different purposes:

/var/lib/postgresql/  # Default data directory
/var/log/postgresql/  # Log files
/usr/share/postgresql/ # Documentation and examples

After cleanup, verify everything works:

sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT version();"
rails db:migrate # Test your Rails connection

---

When PostgreSQL installs on Ubuntu/Linux systems, it creates several standard directories:

/etc/postgresql/     # Configuration files
/usr/lib/postgresql/ # Binary files and libraries
/var/lib/postgresql/ # Data directory

The /opt location is unusual for standard PostgreSQL installations and likely indicates either:

• A manual installation from source
• A third-party packaged version
• Remnants from a previous incomplete uninstallation

First, identify which PostgreSQL instances are actually running:

sudo systemctl list-units --type=service --all | grep postgres

Sample output might show multiple services like:

postgresql.service          loaded active running PostgreSQL RDBMS
postgresql@12-main.service  loaded active running PostgreSQL Cluster 12-main

To find all PostgreSQL-related directories:

sudo find / -name "*postgres*" -type d 2>/dev/null | grep -v "/proc/"

For more targeted search:

sudo updatedb
locate bin/postgres | grep -v snap

Check your Rails connection with:

rails console
ActiveRecord::Base.connection_config

Cross-reference this with your PostgreSQL versions:

psql --version
/usr/lib/postgresql/12/bin/psql --version
/opt/postgresql/bin/psql --version

For standard Ubuntu packages:

sudo apt-get purge postgresql-*
sudo apt-get autoremove

For manually installed versions in /opt:

sudo rm -rf /opt/postgresql
# Also clean up environment variables
sed -i '/POSTGRES_HOME/d' ~/.bashrc

After removal, ensure proper functionality:

sudo systemctl restart postgresql
psql -U postgres -c "SELECT version();"

Update your Ruby pg gem configuration if needed:

gem uninstall pg
gem install pg -- --with-pg-config=/usr/lib/postgresql/12/bin/pg_config

Remove orphaned configs:

sudo rm -rf /etc/postgresql/old_version/
sudo rm /etc/init.d/postgresql.old