Help migrating Node.js/Postgres to 2.0

HartiganHM · November 30, 2018, 2:39pm

Hello! I have a small side project that used to run 1.0 and am looking to update to 2.0. I’ve tried updating on my own through the docs as well as by using the transition generated file from CircleCI, but am hitting a bit of a roadblock. Below is my original yml, as well as where I am in trying to migrate over to 2.0. Any help/advice is appreciated! I’m also happy to provide more details as necessary. Thanks!

EDIT
To give further detail on some of the issues I’m experiencing, the error I’m currently running into is the following:

$ #!/bin/bash -eo pipefail
  sudo -u postgres psql -p 5432 -c "create database app_test;"

/bin/bash: sudo: command not found
Exited with code 127

Original 1.0

machine:
  timezone:
    America/Denver
  node:
    version: 8.3.0

environment:
  DATABASE_URL: postgresql://ubuntu:@127.0.0.1:5432/app_test

database:
  override:
    - sudo -u postgres psql -p 5432 -c "create database app_test;"
    - knex migrate:latest

test:
  override:
    - npm test
    - npm run eslint

deployment:
  staging:
    branch: master
    heroku:
      appname: app

Attempted 2.0

version: 2
jobs:
  build:
    docker:
      - image: node:8.3.0
        environment:
          DATABASE_URL: postgresql://ubuntu:@127.0.0.1:5432/app_test

    working_directory: ~/repo

    steps:
      - checkout
      - run: sudo -u postgres psql -p 5432 -c "create database app_test;"
      - run: knex migrate:latest

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

      - run: npm install

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

      # run tests
      - run: npm test

      # run eslint
      - run: npm run eslint

halfer · November 30, 2018, 8:43pm

What are you stuck on, and what does not work?

In general, readers won’t download it and fix it for you, but they will give advice if you can be specific about what happens when you run it.

HartiganHM · November 30, 2018, 9:19pm

@halfer Thanks for the advice! I updated with the current error log that I’m running into when pushing up to CircleCI.

halfer · December 1, 2018, 4:36pm

Try removing sudo -u - your default user should have enough permissions to run a database query. The error is that the sudo command is not available, so try it without.

However, it looks like it you only have a build Docker image. I wonder if you are expecting PostgreSQL container to spin up also? If so, you’ll need to set up that as a second image within your docker section.

Once you have done that, you may need a run step to wait for it to become available - containers can spin up in parallel, and if you do not wait, your SQL command may run before the database is ready to receive it.

HartiganHM · December 2, 2018, 6:41pm

@halfer Thanks for the help and advice! I added a second image for PostgreSQL and removed sudo -u. When removing sudo -u I get this error:

#!/bin/bash -eo pipefail
postgres psql -p 5432 -c "create database app_test;"
"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise.  See the documentation for
more information on how to properly start the server.
Exited with code 1

If I add sudo -u back, I get the original error that I posted. Currently, this is the state of my yml:

version: 2
jobs:
  build:
    docker:
      - image: circleci/postgres:10-alpine-postgis
      
      - image: node:8.3.0
        environment:
          DATABASE_URL: postgresql://ubuntu:@127.0.0.1:5432/app_test

    working_directory: ~/repo

    steps:
      - checkout
      - run: postgres psql -p 5432 -c "create database app_test;"
      - run: knex migrate:latest

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

      - run: npm install

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

      # run tests
      - run: npm test

      # run eslint
      - run: npm run eslint

Thanks for all of the help!

halfer · December 3, 2018, 6:25pm

OK, great - that is better. However, the images are the wrong way around. The first one needs to be your build container, and then all other ones can be in any order afterwards. I assume the node image is your build container, and you would connect from there to Postgres.

There should be an example of how to start PostgreSQL under a non-root user in the docs. If you get stuck though, try sh -c "command" postgres.

HartiganHM · December 4, 2018, 1:57pm

Thanks for the continued help! I gave it another go with a few options and ran into a couple of different errors (not sure which path to head down). I read over CircleCI doc examples for PostgreSQL and some community solutions, but couldn’t get past a couple of areas. Option #1 is likely where I feel most comfortable as I’m not sure if the post referenced in Option #2 is headed in the right direction.

I also wasn’t sure where to add the line you mentioned (sh -c "command" postgres). I tried it in a couple of places, but it didn’t seem to resolve any issues. Below are the two options I tried and the errors I received.

Thanks again!

Option 1
Insight from these docs

yml

version: 2
jobs:
  build:
    docker:
      - image: node:8.3.0
        environment:
          DATABASE_URL: postgresql://ubuntu:@127.0.0.1:5432/spirit_test

      - image: circleci/postgres:10-alpine-postgis

    working_directory: ~/repo

    steps:
      - checkout
      - run: apt-get update
      - run: apt-get install postgresql-client-10
      - run: postgres psql -p 5432 -c "create database spirit_test;"
      - run: knex migrate:latest

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

      - run: npm install

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

      # run tests
      - run: npm test

      # run eslint
      - run: npm run eslint

Error

#!/bin/bash -eo pipefail
apt-get install postgresql-client-10
Reading package lists... Done


Building dependency tree       


Reading state information... Done

E: Unable to locate package postgresql-client-10
Exited with code 100

Option 2
Insight from this post

yml

version: 2
jobs:
  build:
    docker:
      - image: node:8.3.0
        environment:
          DATABASE_URL: postgresql://ubuntu:@127.0.0.1:5432/app_test

      - image: circleci/postgres:9.6.5-alpine-ram

    working_directory: ~/repo

    steps:
      - checkout
      - run: echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" | tee /etc/apt/sources.list.d/pgdg.list
      - run: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
      - run: apt-get update
      - run: apt-get install postgresql-client-9.6

      - run: postgres psql -p 5432 -c "create database app_test;"
      - run: knex migrate:latest

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

      - run: npm install

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

      # run tests
      - run: npm test

      # run eslint
      - run: npm run eslint

Error

#!/bin/bash -eo pipefail
apt-get install postgresql-client-9.6
Reading package lists... Done


Building dependency tree       


Reading state information... Done

The following extra packages will be installed:
  libpq-dev libpq5 lsb-release pgdg-keyring postgresql-client-common
Suggested packages:
  postgresql-doc-11 lsb postgresql-9.6 postgresql-doc-9.6
The following NEW packages will be installed:
  lsb-release pgdg-keyring postgresql-client-9.6 postgresql-client-common
The following packages will be upgraded:
  libpq-dev libpq5
2 upgraded, 4 newly installed, 0 to remove and 118 not upgraded.
Need to get 1734 kB of archives.
After this operation, 5675 kB of additional disk space will be used.
Do you want to continue? [Y/n] Abort.
Exited with code 1

halfer · December 4, 2018, 8:54pm

I can see two issues here - they are Linux issues, not CI issues.

If a package is not available, then it’s worth looking at what distribution is used to create the parent. You’re requesting the installation of postgresql-client-10 on the Node image. You may need to SSH in to find out what the package is actually called. You may also need to change images so that the package you need is available.
The Y/n question comes from using install without the -y option. If you add that then it will answer “yes” automatically for you - since you want an automated install, any prompts will cause the process to fail.

HartiganHM · December 7, 2018, 1:29pm

Alright, slowly but surely getting there, haha. I went with your second bullet point, add the -y option and am now receiving this error. It’s also worth noting that I removed the postgres command that proceeded psql because I kept getting the postgres command not found. In this guide, they use psql without postgres, which lead to that decision.

#!/bin/bash -eo pipefail
psql -p 5432 -c "create database spirit_test;"
psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Exited with code 2

Current yml is this:

version: 2
jobs:
  build:
    docker:
      - image: node:8.3.0
        environment:
          DATABASE_URL: postgresql://ubuntu:@127.0.0.1:5432/spirit_test

      - image: circleci/postgres:9.6.5-alpine-ram

    working_directory: ~/repo

    steps:
      - checkout
      - run: echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" | tee /etc/apt/sources.list.d/pgdg.list
      - run: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
      - run: apt-get update
      - run: apt-get install postgresql-client-9.6 -y

      - run: psql -p 5432 -c "create database spirit_test;"
      - run: knex migrate:latest

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

      - run: npm install

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

      # run tests
      - run: npm test

      # run eslint
      - run: npm run eslint

halfer · December 7, 2018, 6:25pm

I think that is the problem. Unix sockets don’t work across machines - you need to find a way to force the server (or the client) to use TCP instead.

I don’t know how to do that off the top of my head, but this search looks useful. Let us know how you get on.

HartiganHM · December 15, 2018, 8:01pm

Finally got it working! Thank you for the consistent help. After a lot of digging, I ended up fixing the last issue by adjusting to this: - run: psql -U postgres -h localhost -p 5432 -c "create database spirit_test;", which forced to TCP.

I ran into a couple of other errors along the way with unknown commands/definitions for knex and ubuntu, which were fixed by installing knex globally in the yml and adjusting the DATABASE_URL respectively. Below is the final product! Very excited to finally get this working, thanks again, @halfer!

version: 2
jobs:
  build:
    docker:
      - image: node:8.3.0
        environment:
          DATABASE_URL: postgresql://root@localhost/spirit_test
          POSTGRES_USER: root
          POSTGRES_DB: spirit_test

      - image: circleci/postgres:9.6.5-alpine-ram

    working_directory: ~/repo

    steps:
      - checkout

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

      - run: npm install

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

      - run: echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" | tee /etc/apt/sources.list.d/pgdg.list
      - run: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
      - run: apt-get update
      - run: apt-get install postgresql-client-9.6 -y
      - run: psql -U postgres -h localhost -p 5432 -c "create database spirit_test;"
      - run: npm install -g knex
      - run: knex migrate:latest --env test

      # run tests
      - run: npm test

      # run eslint
      - run: npm run eslint

halfer · December 15, 2018, 8:08pm

Nice work!

system · December 25, 2018, 8:08pm

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

Topic		Replies	Views
Late migrating to Circle 2.0 and having trouble Build Environment nodejs , circle-yml , 2.0	13	2063	September 24, 2018
Can not run migrate in CircleCI with knex migration Databases nodejs , postgresql , circle-yml	0	4413	May 24, 2019
Help config my node js postgre please Build Environment nodejs , circle-yml	1	1025	June 18, 2018
Setting up Postgres for NodeJS [ Error: "sudo: unknown user: postgres"] Databases nodejs , postgresql	2	4629	June 18, 2018
Seeding Postgres database and connect to Node Databases nodejs , docker , postgresql , circle-yml	2	5500	June 18, 2018

Help migrating Node.js/Postgres to 2.0

Related topics