When attempting to connect to a PostgreSQL 8.3 server from an Ubuntu 11.10 x86_64 client (using PostgreSQL 9.1 libraries) via command line, users encounter:

psql: FATAL: could not write init file

The command triggering this error:

psql -U postgres -d my_database -h 192.168.0.161 -p 5432 -c "select * from xxyy"

The key difference lies in how these clients handle temporary files. pgAdmin manages its own temporary files independently of system permissions, while psql relies on system temp directories.

First check the default temp directory permissions:

ls -ld /tmp

Then verify your user's temporary directory:

echo $TMPDIR

Option 1: Change the temporary directory

export TMPDIR=/your/writable/path
psql -U postgres -d my_database -h 192.168.0.161 -p 5432

Option 2: Fix system temp permissions

sudo chmod 1777 /tmp

Option 3: Disable .psql_history writing

psql --no-psqlrc -U postgres -d my_database -h 192.168.0.161 -p 5432

To see exactly where psql tries to write:

PSQL_DEBUG=1 psql -U postgres -d my_database -h 192.168.0.161 -p 5432

This will output detailed debugging information about file operations.

Add this to your .bashrc or equivalent:

export PSQL_HISTORY=~/.psql_history_$USER
export TMPDIR=~/tmp
mkdir -p $TMPDIR

For persistent cases, you might need to debug psql itself:

sudo apt-get build-dep postgresql-client-9.1
apt-get source postgresql-client-9.1
cd postgresql-9.1*
./configure --enable-debug
make

Then run the custom-built psql with gdb for detailed tracing.

---

When attempting to connect to a PostgreSQL 8.3 server from an Ubuntu 11.10 client (using psql 9.1 libraries) with this command:

psql -U postgres -d my_database -h 192.168.0.161 -p 5432 -c "select * from xxyy"

The system throws the frustrating error: psql: FATAL: could not write init file. What's particularly puzzling is that pgAdmin connects successfully while the command-line client fails.

This error typically occurs when the PostgreSQL client (psql) cannot create temporary files in its default working directory. The init file (usually .psqlrc or similar) is essential for session initialization.

Key factors to investigate:

• Permission issues in the /tmp directory or user's home directory
• Disk space or inode exhaustion
• SELinux/AppArmor restrictions (though less common on Ubuntu)
• Filesystem mount options (noexec, nodev flags)

1. Verify Directory Permissions

First check the target directory permissions where psql tries to write:

ls -ld ~ /tmp
stat -c "%a %U %G" ~ /tmp

2. Test with Explicit Temp Directory

Force psql to use a specific temporary directory:

export PSQL_TMPDIR=/var/tmp
psql -U postgres -h 192.168.0.161 -c "SELECT version()"

3. Check Filesystem Health

Verify disk space and inode availability:

df -h
df -i

Option 1: Configure Default Directory

Edit the PostgreSQL client environment configuration:

echo "export PSQL_TMPDIR=/var/lib/postgresql/tmp" >> ~/.bashrc
mkdir -p /var/lib/postgresql/tmp
chown postgres:postgres /var/lib/postgresql/tmp

Option 2: System-wide Fix

For multi-user systems, modify the global temp directory:

sudo mkdir /postgres_tmp
sudo chmod 1777 /postgres_tmp
echo "export TMPDIR=/postgres_tmp" | sudo tee /etc/profile.d/pgtmp.sh

If immediate resolution isn't possible, consider these workarounds:

# Using environment variables
PGPASSWORD=mypassword psql -h 192.168.0.161 -U postgres -d my_database

# Using connection URI format
psql postgresql://postgres@192.168.0.161:5432/my_database

Enable verbose logging to pinpoint the exact failure point:

psql --set=VERBOSITY=verbose -U postgres -h 192.168.0.161

Or use strace for low-level debugging:

strace -f -o psql_trace.log psql -U postgres -h 192.168.0.161