Build A Periodic Table DB - Trouble Re-building from SQL dump

Backend Development
1

Hello all,
I am having trouble correctly re-building the database I was working on and dumped yesterday. I used the given command to dump the file
psql --username=freecodecamp --dbname=periodic_table
I had some trouble restoring the file as it default kept restoring to just the postgres database using the given command
psql -U postgres < periodic_table.sql
after adding a -d periodic_table tag to the first part I was able to get it to populate to the proper database, however, I was met with tons of errors and it seems only about half of my changes got correctly re-applied. Many of the things failing being the first few steps like re-naming a column.

ErrDump
ErrDump712Ă—687 31 KB

Any insight into why my dump failed to properly re-build or more reading I can do on the subject? Attatching the bash terminal if it helps at all

EDIT: I am thinking current restrictions on the database aren’t allowing me to restore correctly, and that I need to drop the database completely in order to re-store it, it seems I don’t have ownership of the database to do this though, perhaps an answer to that could help my issue.

2

Hey @dmag171, can you share a link to the dump you created? I will give it a try.

I was met with tons of errors and it seems only about half of my changes got correctly re-applied. Many of the things failing being the first few steps like re-naming a column.

That makes sense. I bet if you drop the database first, it would work.

Are you sure you used the correct command to dump the db: pg_dump -cC --inserts -U freecodecamp periodic_table > periodic_table.sql ? that -c is supposed to add the command to drop the database before it tries to rebuild it. I see DROP DATABASE periodic_table; as one of the first things in the dump of my db that I created using that command.

Okay, this may be the problem - I tried to rebuild the database using my dump and got this: ERROR: database "periodic_table" is being accessed by other users, along with a bunch of other errors like your image. It couldn’t drop the database cause I was logging into it. It worked after I logged out of the psql shell. I would make sure you are logged out of that and give it another try @dmag171. Let us know if that works.

3

I had mentioned the wrong command in my original post but I do think i messed up the original dump command, I do not see any element to DROP DATABASE in my sql dump, can this be manually added?

How can i go about easily linking you the dump? The download button when right clicking the file in vscode doesn’t seem to do anything

4

You need to copy all the code/text in the file and save it somewhere public where I can see it. You could just paste it all in a message here if you want. Although, that makes it a little cluttered.

You can add the command drop the database in your file - In mine, it’s on line 19 right after some row security line. It looks like this:

...
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE periodic_table;

You could also just drop the database manually in the psql prompt before you try to rebuild it. That should work, too.

5

I think the issue has to do with how I dumped it because it doesn’t correctly populate back into the correct database either unless I direct it with the -d tag, otherwise postgres gets all of my tables.

EDIT: I’m also unable to manually drop the database from the bash terminal, it says I don’t have ownership, can only drop it when I add it into the dump code it seems.

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)
-- Dumped by pg_dump version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE periodic_table;
SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: elements; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.elements (
    atomic_number integer NOT NULL,
    symbol character varying(2) NOT NULL,
    name character varying(40) NOT NULL
);


ALTER TABLE public.elements OWNER TO freecodecamp;

--
-- Name: properties; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.properties (
    atomic_number integer NOT NULL,
    type character varying(30),
    atomic_mass numeric(9,6) NOT NULL,
    melting_point_celsius numeric NOT NULL,
    boiling_point_celsius numeric NOT NULL,
    type_id integer NOT NULL
);


ALTER TABLE public.properties OWNER TO freecodecamp;

--
-- Name: types; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.types (
    type character varying NOT NULL,
    type_id integer NOT NULL
);


ALTER TABLE public.types OWNER TO freecodecamp;

--
-- Name: types_type_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.types_type_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.types_type_id_seq OWNER TO freecodecamp;

--
-- Name: types_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.types_type_id_seq OWNED BY public.types.type_id;


--
-- Name: types type_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.types ALTER COLUMN type_id SET DEFAULT nextval('public.types_type_id_seq'::regclass);


--
-- Data for Name: elements; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.elements VALUES (1, 'H', 'Hydrogen');
INSERT INTO public.elements VALUES (2, 'He', 'Helium');
INSERT INTO public.elements VALUES (3, 'Li', 'Lithium');
INSERT INTO public.elements VALUES (4, 'Be', 'Beryllium');
INSERT INTO public.elements VALUES (5, 'B', 'Boron');
INSERT INTO public.elements VALUES (6, 'C', 'Carbon');
INSERT INTO public.elements VALUES (7, 'N', 'Nitrogen');
INSERT INTO public.elements VALUES (8, 'O', 'Oxygen');
INSERT INTO public.elements VALUES (1000, 'MT', 'Motanium');


--
-- Data for Name: properties; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.properties VALUES (3, 'metal', 6.940000, 180.54, 1342, 1);
INSERT INTO public.properties VALUES (4, 'metal', 9.012200, 1287, 2470, 1);
INSERT INTO public.properties VALUES (1, 'nonmetal', 1.008000, -259.1, -252.9, 2);
INSERT INTO public.properties VALUES (2, 'nonmetal', 4.002600, -272.2, -269, 2);
INSERT INTO public.properties VALUES (6, 'nonmetal', 12.011000, 3550, 4027, 2);
INSERT INTO public.properties VALUES (7, 'nonmetal', 14.007000, -210.1, -195.8, 2);
INSERT INTO public.properties VALUES (8, 'nonmetal', 15.999000, -218, -183, 2);
INSERT INTO public.properties VALUES (5, 'metalloid', 10.810000, 2075, 4000, 3);
INSERT INTO public.properties VALUES (1000, 'metalloid', 1.000000, 10, 100, 3);


--
-- Data for Name: types; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.types VALUES ('metal', 1);
INSERT INTO public.types VALUES ('nonmetal', 2);
INSERT INTO public.types VALUES ('metalloid', 3);


--
-- Name: types_type_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.types_type_id_seq', 3, true);


--
-- Name: elements elements_atomic_number_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.elements
    ADD CONSTRAINT elements_atomic_number_key UNIQUE (atomic_number);


--
-- Name: elements elements_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.elements
    ADD CONSTRAINT elements_name_key UNIQUE (name);


--
-- Name: elements elements_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.elements
    ADD CONSTRAINT elements_pkey PRIMARY KEY (atomic_number);


--
-- Name: elements elements_symbol_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.elements
    ADD CONSTRAINT elements_symbol_key UNIQUE (symbol);


--
-- Name: properties properties_atomic_number_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.properties
    ADD CONSTRAINT properties_atomic_number_key UNIQUE (atomic_number);


--
-- Name: properties properties_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.properties
    ADD CONSTRAINT properties_pkey PRIMARY KEY (atomic_number);


--
-- Name: types types_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.types
    ADD CONSTRAINT types_pkey PRIMARY KEY (type_id);


--
-- Name: properties properties_atomic_number_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.properties
    ADD CONSTRAINT properties_atomic_number_fkey FOREIGN KEY (atomic_number) REFERENCES public.elements(atomic_number);


--
-- Name: properties properties_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.properties
    ADD CONSTRAINT properties_type_id_fkey FOREIGN KEY (type_id) REFERENCES public.types(type_id);


--
-- PostgreSQL database dump complete
--
6

Replace your file with this @dmag171:

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)
-- Dumped by pg_dump version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE periodic_table;
--
-- Name: elements; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE DATABASE periodic_table WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8';


ALTER DATABASE periodic_table OWNER TO postgres;

\connect periodic_table

CREATE TABLE public.elements (
    atomic_number integer NOT NULL,
    symbol character varying(2) NOT NULL,
    name character varying(40) NOT NULL
);


ALTER TABLE public.elements OWNER TO freecodecamp;

--
-- Name: properties; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.properties (
    atomic_number integer NOT NULL,
    type character varying(30),
    atomic_mass numeric(9,6) NOT NULL,
    melting_point_celsius numeric NOT NULL,
    boiling_point_celsius numeric NOT NULL,
    type_id integer NOT NULL
);


ALTER TABLE public.properties OWNER TO freecodecamp;

--
-- Name: types; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.types (
    type character varying NOT NULL,
    type_id integer NOT NULL
);


ALTER TABLE public.types OWNER TO freecodecamp;

--
-- Name: types_type_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.types_type_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.types_type_id_seq OWNER TO freecodecamp;

--
-- Name: types_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.types_type_id_seq OWNED BY public.types.type_id;


--
-- Name: types type_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.types ALTER COLUMN type_id SET DEFAULT nextval('public.types_type_id_seq'::regclass);


--
-- Data for Name: elements; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.elements VALUES (1, 'H', 'Hydrogen');
INSERT INTO public.elements VALUES (2, 'He', 'Helium');
INSERT INTO public.elements VALUES (3, 'Li', 'Lithium');
INSERT INTO public.elements VALUES (4, 'Be', 'Beryllium');
INSERT INTO public.elements VALUES (5, 'B', 'Boron');
INSERT INTO public.elements VALUES (6, 'C', 'Carbon');
INSERT INTO public.elements VALUES (7, 'N', 'Nitrogen');
INSERT INTO public.elements VALUES (8, 'O', 'Oxygen');
INSERT INTO public.elements VALUES (1000, 'MT', 'Motanium');


--
-- Data for Name: properties; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.properties VALUES (3, 'metal', 6.940000, 180.54, 1342, 1);
INSERT INTO public.properties VALUES (4, 'metal', 9.012200, 1287, 2470, 1);
INSERT INTO public.properties VALUES (1, 'nonmetal', 1.008000, -259.1, -252.9, 2);
INSERT INTO public.properties VALUES (2, 'nonmetal', 4.002600, -272.2, -269, 2);
INSERT INTO public.properties VALUES (6, 'nonmetal', 12.011000, 3550, 4027, 2);
INSERT INTO public.properties VALUES (7, 'nonmetal', 14.007000, -210.1, -195.8, 2);
INSERT INTO public.properties VALUES (8, 'nonmetal', 15.999000, -218, -183, 2);
INSERT INTO public.properties VALUES (5, 'metalloid', 10.810000, 2075, 4000, 3);
INSERT INTO public.properties VALUES (1000, 'metalloid', 1.000000, 10, 100, 3);


--
-- Data for Name: types; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.types VALUES ('metal', 1);
INSERT INTO public.types VALUES ('nonmetal', 2);
INSERT INTO public.types VALUES ('metalloid', 3);


--
-- Name: types_type_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.types_type_id_seq', 3, true);


--
-- Name: elements elements_atomic_number_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.elements
    ADD CONSTRAINT elements_atomic_number_key UNIQUE (atomic_number);


--
-- Name: elements elements_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.elements
    ADD CONSTRAINT elements_name_key UNIQUE (name);


--
-- Name: elements elements_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.elements
    ADD CONSTRAINT elements_pkey PRIMARY KEY (atomic_number);


--
-- Name: elements elements_symbol_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.elements
    ADD CONSTRAINT elements_symbol_key UNIQUE (symbol);


--
-- Name: properties properties_atomic_number_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.properties
    ADD CONSTRAINT properties_atomic_number_key UNIQUE (atomic_number);


--
-- Name: properties properties_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.properties
    ADD CONSTRAINT properties_pkey PRIMARY KEY (atomic_number);


--
-- Name: types types_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.types
    ADD CONSTRAINT types_pkey PRIMARY KEY (type_id);


--
-- Name: properties properties_atomic_number_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.properties
    ADD CONSTRAINT properties_atomic_number_fkey FOREIGN KEY (atomic_number) REFERENCES public.elements(atomic_number);


--
-- Name: properties properties_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.properties
    ADD CONSTRAINT properties_type_id_fkey FOREIGN KEY (type_id) REFERENCES public.types(type_id);


--
-- PostgreSQL database dump complete
--

I changed a couple lines between lines 24-27ish. Try it again after that: psql -U postgres < periodic_table.sql. Make sure you aren’t logged into the database with another terminal. Let me know if you still can’t figure it out or something else isn’t quite right - or if it works.

7

This worked perfect, going back over my work it definitely had to do with the dump command being inputted incorrectly. I’ve just tested now making another dump file with the correct command and it has all the missing elements i needed that you manually added back in.
Thank you very much for the help i greatly appreciate it!

1 Like
8

Hi, I have rebuilt my project for the 3rd time now thinking that I didn’t dump it properly. This time I can definitely see it in the folder with all the git command as I committed it.
Well, I still cannot connect to it and could only connect to the original sql file.

I read this thread and know that I can drop the database from the terminal, is this correct?
Also, my sql file has all the set statements repeated twice. Is this normal?

9

now I get a server connection error, maybe connected too many times. So to ask my question correctly is: (1) should I still connect like normal to the initial database?
(2) Drop the database?
(3) And then rebuild my own database using psql -U postgres < periodic_table.sql ?

Will appreciate any help, thank you very much.

codeally@a3e1d6b9f22a:~/project$ psql --username=freecodecamp --dbname=periodic_table
psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: No such file or directory
Is the server running locally and accepting connections on that socket?
codeally@a3e1d6b9f22a:~/project$