Create
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Product Q&A
See all
Community resources
Top groups
Explore all groups
Community resources
Learn
Community resources
Events
Community resources

pg_restore in pipeline causes build to fail

Doug Svacina
Doug Svacina September 4, 2024

I'm trying to run a Postgres service in my Pipeline where I created all my test data in a db I'm running on my local machine. I ran a pg_dump to create test data tar file. When I try to pg_restore that tar file in my pipeline, it looks like it works, but the restore script fails and my pipeline errors.

Here is my pipeline file: 

image: node:20.10.0
definitions:
  services:
    postgres:
      image: postgres:16
      environment:
        POSTGRES_DB: reporting
        POSTGRES_USER: postgres
        POSTGRES_PASSWORD: mysecretpassword
options:
  docker: true
pipelines:
  default:
    - step:
       caches:
         - docker
       script: 
         - apt-get update && apt-get install -y postgresql-client
         - export PGPASSWORD='mysecretpassword'
         - npm install
         - pg_restore -U postgres -d reporting -h localhost -c -C -v --no-owner --no-acl ./tests/databaseScripts/reportingDump.tar
         - npm run unit-tests
         - npm run integration-tests
      services:
         - postgres

And here is the Pipeline output:

 + pg_restore -U postgres -d reporting -h localhost -c -C -v --no-owner --no-acl ./tests/databaseScripts/reportingDump.tar 
pg_restore: connecting to database for restore 
pg_restore: dropping DATABASE reporting pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 3422; 1262 16384 DATABASE reporting postgres pg_restore: error: could not execute query: ERROR: cannot drop the currently open database Command was: DROP DATABASE reporting; 
pg_restore: creating DATABASE "reporting" 
pg_restore: error: could not execute query: ERROR: database "reporting" already exists Command was: CREATE DATABASE reporting WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.utf8'; pg_restore: connecting to new database "reporting" 
pg_restore: creating SCHEMA "public" pg_restore: creating FUNCTION "public.f_getDataForDeviationCalculation(character varying, date, date)" 
pg_restore: creating TABLE "public.Deck" 
pg_restore: creating TABLE "public.Impressions" 
pg_restore: creating TABLE "public.Presentation-Named" 
pg_restore: creating TABLE "public.PresentationSlide" 
pg_restore: creating TABLE "public.Session" 
pg_restore: creating TABLE "public.Slide" 
pg_restore: creating TABLE "public.User" 
pg_restore: processing data for table "public.Deck" 
pg_restore: processing data for table "public.Impressions" 
pg_restore: processing data for table "public.Presentation-Named" 
pg_restore: processing data for table "public.PresentationSlide" 
pg_restore: processing data for table "public.Session" 
pg_restore: processing data for table "public.Slide" 
pg_restore: processing data for table "public.User" 
pg_restore: creating CONSTRAINT "public.Deck Deck_pkey" 
pg_restore: creating CONSTRAINT "public.Impressions Impressions_pkey" pg_restore: creating CONSTRAINT "public.Presentation-Named Presentation-Named_pkey" 
pg_restore: creating CONSTRAINT "public.PresentationSlide PresentationSlide_pkey" 
pg_restore: creating CONSTRAINT "public.Session Session_pkey" 
pg_restore: creating CONSTRAINT "public.Slide Slide_pkey" 
pg_restore: creating CONSTRAINT "public.User User_pkey" 
pg_restore: warning: errors ignored on restore: 2 
Skipping cache upload for failed step 
Searching for test report files in directories named [test-results, failsafe-reports, test-reports, TestResults, surefire-reports] down to a depth of 4 
Finished scanning for test reports. Found 0 test report files. 
Merged test suites, total number tests is 0, with 0 failures and 0 errors.

I see that there is an error trying to drip the reporting database, but that doesn't seem to stop the restore from running. It's still creating the tables, loading the data and adding the constraints. Can someone tell me why this step is failing my pipeline?

 

Answer
Watch
Like Be the first to like this
374 views

1 answer

0 votes
Mark C
Mark C
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
September 6, 2024

Hi @Doug Svacina

Welcome to the community.

When you tried to run your build locally, may I know if you run in a Docker container?

Regards,
Mark C

View More Comments
Doug Svacina
Doug Svacina September 6, 2024

Yup... I'm using dev containers in Visual Studio Code. I'm using 

node:20.15.1 as my dev container image.

This is my dev container Dockerfile:

FROM node:20.15.1

RUN apt-get update && apt-get install -y postgresql-client

RUN curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip" \

&& unzip awscliv2.zip \

&& ./aws/install
Like
Mark C
Mark C
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
September 9, 2024

Hi @Doug Svacina

Thanks for that.

For the pg_restore command, could you try to remove the "-C" flag and let's see if that helps?

Regards,
Mark C

Like
Doug Svacina
Doug Svacina September 11, 2024

I get pretty much the same result without the -C flag. A few more errors, but it seems like it recovered from them as it did create the tables and constraints.

pg_restore -U postgres -d reporting -h localhost -c -v --no-owner --no-acl ./tests/databaseScripts/reportingDump.tar
pg_restore: connecting to database for restore
pg_restore: dropping CONSTRAINT User User_pkey
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3270; 2606 17079 CONSTRAINT User User_pkey postgres
pg_restore: error: could not execute query: ERROR: relation "public.User" does not exist
Command was: ALTER TABLE ONLY public."User" DROP CONSTRAINT "User_pkey";
pg_restore: dropping CONSTRAINT Slide Slide_pkey
pg_restore: from TOC entry 3268; 2606 17077 CONSTRAINT Slide Slide_pkey postgres
pg_restore: error: could not execute query: ERROR: relation "public.Slide" does not exist
Command was: ALTER TABLE ONLY public."Slide" DROP CONSTRAINT "Slide_pkey";
pg_restore: dropping CONSTRAINT Session Session_pkey
pg_restore: from TOC entry 3266; 2606 17075 CONSTRAINT Session Session_pkey postgres
pg_restore: error: could not execute query: ERROR: relation "public.Session" does not exist
Command was: ALTER TABLE ONLY public."Session" DROP CONSTRAINT "Session_pkey";
pg_restore: dropping CONSTRAINT PresentationSlide PresentationSlide_pkey
pg_restore: from TOC entry 3264; 2606 17073 CONSTRAINT PresentationSlide PresentationSlide_pkey postgres
pg_restore: error: could not execute query: ERROR: relation "public.PresentationSlide" does not exist
Command was: ALTER TABLE ONLY public."PresentationSlide" DROP CONSTRAINT "PresentationSlide_pkey";
pg_restore: dropping CONSTRAINT Presentation-Named Presentation-Named_pkey
pg_restore: from TOC entry 3262; 2606 17071 CONSTRAINT Presentation-Named Presentation-Named_pkey postgres
pg_restore: error: could not execute query: ERROR: relation "public.Presentation-Named" does not exist
Command was: ALTER TABLE ONLY public."Presentation-Named" DROP CONSTRAINT "Presentation-Named_pkey";
pg_restore: dropping CONSTRAINT Impressions Impressions_pkey
pg_restore: from TOC entry 3260; 2606 17069 CONSTRAINT Impressions Impressions_pkey postgres
pg_restore: error: could not execute query: ERROR: relation "public.Impressions" does not exist
Command was: ALTER TABLE ONLY public."Impressions" DROP CONSTRAINT "Impressions_pkey";
pg_restore: dropping CONSTRAINT Deck Deck_pkey
pg_restore: from TOC entry 3258; 2606 17067 CONSTRAINT Deck Deck_pkey postgres
pg_restore: error: could not execute query: ERROR: relation "public.Deck" does not exist
Command was: ALTER TABLE ONLY public."Deck" DROP CONSTRAINT "Deck_pkey";
pg_restore: dropping TABLE User
pg_restore: from TOC entry 215; 1259 16970 TABLE User postgres
pg_restore: error: could not execute query: ERROR: table "User" does not exist
Command was: DROP TABLE public."User";
pg_restore: dropping TABLE Slide
pg_restore: from TOC entry 214; 1259 16965 TABLE Slide postgres
pg_restore: error: could not execute query: ERROR: table "Slide" does not exist
Command was: DROP TABLE public."Slide";
pg_restore: dropping TABLE Session
pg_restore: from TOC entry 213; 1259 16960 TABLE Session postgres
pg_restore: error: could not execute query: ERROR: table "Session" does not exist
Command was: DROP TABLE public."Session";
pg_restore: dropping TABLE PresentationSlide
pg_restore: from TOC entry 212; 1259 16955 TABLE PresentationSlide postgres
pg_restore: error: could not execute query: ERROR: table "PresentationSlide" does not exist
Command was: DROP TABLE public."PresentationSlide";
pg_restore: dropping TABLE Presentation-Named
pg_restore: from TOC entry 211; 1259 16950 TABLE Presentation-Named postgres
pg_restore: error: could not execute query: ERROR: table "Presentation-Named" does not exist
Command was: DROP TABLE public."Presentation-Named";
pg_restore: dropping TABLE Impressions
pg_restore: from TOC entry 210; 1259 16945 TABLE Impressions postgres
pg_restore: error: could not execute query: ERROR: table "Impressions" does not exist
Command was: DROP TABLE public."Impressions";
pg_restore: dropping TABLE Deck
pg_restore: from TOC entry 209; 1259 16940 TABLE Deck postgres
pg_restore: error: could not execute query: ERROR: table "Deck" does not exist
Command was: DROP TABLE public."Deck";
pg_restore: dropping FUNCTION f_getDataForDeviationCalculation(character varying, date, date)
pg_restore: from TOC entry 227; 1255 16939 FUNCTION f_getDataForDeviationCalculation(character varying, date, date) postgres
pg_restore: error: could not execute query: ERROR: function public.f_getDataForDeviationCalculation(character varying, date, date) does not exist
Command was: DROP FUNCTION public."f_getDataForDeviationCalculation"(deck_id character varying, start_date date, end_date date);
pg_restore: dropping SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: creating FUNCTION "public.f_getDataForDeviationCalculation(character varying, date, date)"
pg_restore: creating TABLE "public.Deck"
pg_restore: creating TABLE "public.Impressions"
pg_restore: creating TABLE "public.Presentation-Named"
pg_restore: creating TABLE "public.PresentationSlide"
pg_restore: creating TABLE "public.Session"
pg_restore: creating TABLE "public.Slide"
pg_restore: creating TABLE "public.User"
pg_restore: processing data for table "public.Deck"
pg_restore: processing data for table "public.Impressions"
pg_restore: processing data for table "public.Presentation-Named"
pg_restore: processing data for table "public.PresentationSlide"
pg_restore: processing data for table "public.Session"
pg_restore: processing data for table "public.Slide"
pg_restore: processing data for table "public.User"
pg_restore: creating CONSTRAINT "public.Deck Deck_pkey"
pg_restore: creating CONSTRAINT "public.Impressions Impressions_pkey"
pg_restore: creating CONSTRAINT "public.Presentation-Named Presentation-Named_pkey"
pg_restore: creating CONSTRAINT "public.PresentationSlide PresentationSlide_pkey"
pg_restore: creating CONSTRAINT "public.Session Session_pkey"
pg_restore: creating CONSTRAINT "public.Slide Slide_pkey"
pg_restore: creating CONSTRAINT "public.User User_pkey"
pg_restore: warning: errors ignored on restore: 15

Like
Mark C
Mark C
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
September 16, 2024

Hi @Doug Svacina

Thanks for trying that.
It seems that there's something wrong with the authentication method.
Could you try to connect to the database before running the command?
Also, see if you could specify the username/password as part of the command and see if it makes a difference.

Regards,
Mark C

Like
Reply

Suggest an answer

Log in or Sign up to answer
Bitbucket
Bitbucket
DEPLOYMENT TYPE
CLOUD
TAGS
AUG Leaders

Atlassian Community Events

    See all events