CircleCi Occasionally Can't Connect To Postgres In Builds

Basically, I have a Nodejs project which connects to a postgres database via knex and I use circleci for my tests. It works, but occasionally a build will fail due to “Error: connect ECONNREFUSED”, and I can’t figure out why, it works without issue on my machine. The file that is run is below:

const knex = require('knex')(connectionOptions);
console.log('New connection to default postgres database made');

// Remove all other connections to test database
knex.raw(`select pg_terminate_backend(pid) from pg_stat_activity where datname = '${process.env.PG_TEST_DATABASE}'`)
	.then(() => {
		console.log('Removed all other connections to the test database');
		// Drop test database if it exists
		return knex.raw(`DROP DATABASE IF EXISTS ${process.env.PG_TEST_DATABASE};`);
	.then(() => {
		console.log('Dropped test database (if it existed)');
		// Create test database
		return knex.raw(`CREATE DATABASE ${process.env.PG_TEST_DATABASE};`);
	.then(() => {
		console.log('Test database created');
		return process.exit();

When it occurs, the error will happen on the first attempt to do anything with postgres after I connect, whether I try to drop a db, remove other connections, etc. Currently, it happens when I try to do the ‘select pg_terminate_backend’ line. Any suggestions would be appreciated. My settings for Postgres are:

const connectionOptions = {
	client: 'pg',
	version: '7.4.1',
	connection: {
		host: '',
		user: process.env.PG_USER,
		password: process.env.PG_PASSWORD,
		database: process.env.PG_DATABASE,
		port: parseInt(process.env.PG_PORT) || 5432
	pool: {
		min: 2,
		max: 10
	migrations: {
		tableName: '_migrations',
		directory: './migrations',
	seeds: {
		directory: './seeds/development'

And My circleci yml file is below:

version: 2
    working_directory: ~/project
      - image: circleci/node:8.9.4
      # The below environemnt is where you set the .env variables used throughout node
          NODE_ENV: test
          PG_USER: jerrodq2
          PG_DATABASE: freelancing_project
          PG_TEST_DATABASE: freelancing_project_test
          PG_PORT: 5432

      - image: postgres:10.3
          POSTGRES_USER: jerrodq2
          POSTGRES_DB: freelancing_project

      - checkout

      # Download and cache dependencies
      - restore_cache:
          - v1-dependencies-{{ checksum "package.json" }}
          # fallback to using the latest cache if no exact match is found
          - v1-dependencies-

      - run:
          name: Install local dependencies
          command: npm install

      - run:
          name: Create database
          command: npm run db:reset:test

      - save_cache:
            - node_modules
          key: v1-dependencies-{{ checksum "package.json" }}

      # run tests!
      - run:
          name: Running Tests
          command: npm run test

Incase anyone is interested, I fixed the issue by adding a command to the yml file so circleci waits for postgres to be open, I added the below line before the create database line:

  - run:
      name: Install local dependencies
      command: npm install

  - run:
      name: Wait for Postgres to start
      command: dockerize -wait tcp://localhost:5432 -timeout 1m

  - run:
      name: Create database
      command: npm run db:reset:test

For reference, this question was cross-posted to Stack Overflow.

1 Like

There’s also a support article about this, but I’ll mark the solution as @jerrodq2’s post since it includes a direct example.

1 Like

This topic was automatically closed 10 days after the last reply. New replies are no longer allowed.