When attempting to install PostgreSQL 9.2.3 on Ubuntu using apt-get, you might encounter the frustrating "package not available" error. This happens because:

• Ubuntu's default repositories often don't contain older PostgreSQL versions
• Package names follow different conventions across Linux distributions
• The exact version (9.2.3) might not exist as a separate package

Before proceeding, verify what PostgreSQL versions are available in your repositories:

apt-cache search postgresql-9

You'll likely see output similar to:

postgresql-9.1 - object-relational SQL database, version 9.1 server
postgresql-9.1-dbg - debug symbols for postgresql-9.1
...

The most reliable solution is to add PostgreSQL's official APT repository:

# Create the repository configuration file
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update your package lists
sudo apt-get update

Now you can install the specific version:

# Install PostgreSQL 9.2 server and client
sudo apt-get install postgresql-9.2 postgresql-client-9.2

# Optionally install common extensions
sudo apt-get install postgresql-contrib-9.2

Check if PostgreSQL 9.2 is running:

sudo service postgresql status

You should see output mentioning postgresql@9.2-main. Connect to the database:

sudo -u postgres psql -c "SELECT version();"

If the repository method doesn't work, you can compile from source:

# Install dependencies
sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison

# Download PostgreSQL 9.2.3 source
wget https://ftp.postgresql.org/pub/source/v9.2.3/postgresql-9.2.3.tar.gz
tar xvfz postgresql-9.2.3.tar.gz
cd postgresql-9.2.3

# Configure and build
./configure
make
sudo make install

After installation, remember to initialize the database cluster and set up the service.

For Ruby on Rails applications, you'll need to:

# Create database user
sudo -u postgres createuser -P -d -r -s your_app_user

# Create database
sudo -u postgres createdb -O your_app_user your_app_development

# Update pg_hba.conf for remote access if needed
sudo nano /etc/postgresql/9.2/main/pg_hba.conf

Add this line for development purposes:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

---

When attempting to install PostgreSQL 9.2 on Ubuntu, you might encounter the frustrating "Package not available" error. This typically happens because:

sudo apt-get install postgresql-9.2
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package postgresql-9.2

Ubuntu's default repositories often only maintain recent PostgreSQL versions. For older versions like 9.2, we need to add PostgreSQL's official repository.

First, ensure you have the required dependencies:

sudo apt-get install wget ca-certificates

Then add the PostgreSQL repository and its key:

# Create the file repository configuration
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists
sudo apt-get update

Now you can install PostgreSQL 9.2:

sudo apt-get install postgresql-9.2 postgresql-client-9.2 postgresql-contrib-9.2

The installation will:
1. Set up the PostgreSQL service
2. Create the postgres user
3. Initialize the database cluster
4. Configure automatic start on boot

Check if PostgreSQL 9.2 is running:

sudo -u postgres psql -c "SELECT version();"

Expected output shows PostgreSQL 9.2.x information.

You'll likely want to:

1. Set a password for the postgres user:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'yourpassword';"

2. Configure remote access if needed by editing:

sudo nano /etc/postgresql/9.2/main/postgresql.conf

Change listen_addresses = 'localhost' to '*'

3. Update pg_hba.conf for authentication:

sudo nano /etc/postgresql/9.2/main/pg_hba.conf

If you encounter issues:

- Check service status:

sudo systemctl status postgresql@9.2-main

- Examine logs:

tail -n 50 /var/log/postgresql/postgresql-9.2-main.log

- Verify port 5432 is listening:

sudo netstat -plnt | grep postgres