Thread: Issues with pg_dump command
Hi, I was running into a problem with the pg_dump command, I am dumping several tables into a file but one table’s data does not appear in the file,
can you please assist me with this issue?
Thank you,
Jonathan
| Jonathan Tauman | M: +972-58-6333-805| |
Attachment
> On 18 Nov 2020, at 13:34, Jonathan Tauman <JonathanT@qmasters.co> wrote: > Hi, I was running into a problem with the pg_dump command, I am dumping several tables into a file but one table’s datadoes not appear in the file, > can you please assist me with this issue? You need to provide a lot more details in order for anyone to be able to help. How are you running pg_dump, which version is it and what does the tables look like etc? If you can create a minimal reproducer which shows the bug then that helps a lot in troubleshooting. cheers ./daniel
We are calling pg_dump for a 9.6.15 PostgreSQL installation (on QRadar).
Here is the call to the command:
pg_dump -U qradar -d qradar -a --disable-triggers -t ariel_property
The dump output looks like this:
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.6.15
-- Dumped by pg_dump version 9.6.15
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;
--
-- Data for Name: ariel_property; Type: TABLE DATA; Schema: public; Owner: qrada
r
--
SET SESSION AUTHORIZATION DEFAULT;
ALTER TABLE public.ariel_property DISABLE TRIGGER ALL;
COPY public.ariel_property (id, propertyname, description, database, username, c
reationdate, editdate, sequenceid, description_id, tenant_id) FROM stdin;
\.
ALTER TABLE public.ariel_property ENABLE TRIGGER ALL;
--
-- PostgreSQL database dump complete
--
The schema dump looks like this:
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.6.15
-- Dumped by pg_dump version 9.6.15
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;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: ariel_property; Type: TABLE; Schema: public; Owner: qradar
--
CREATE TABLE public.ariel_property (
id character varying(255) NOT NULL,
propertyname character varying(255) NOT NULL,
description character varying(1275) NOT NULL,
database character varying(255) NOT NULL,
username character varying(255) NOT NULL,
creationdate bigint NOT NULL,
editdate bigint NOT NULL,
sequenceid bigint DEFAULT nextval('public.ariel_property_seq'::regclass) NOT NULL,
description_id character varying(1275),
tenant_id integer DEFAULT 0 NOT NULL
);
ALTER TABLE public.ariel_property OWNER TO qradar;
--
-- Name: COLUMN ariel_property.id; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.id IS 'code generated UUID';
--
-- Name: COLUMN ariel_property.propertyname; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.propertyname IS 'Name of this property. Appears in UI';
--
-- Name: COLUMN ariel_property.database; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.database IS 'Database this property is for (events, flows)';
--
-- Name: COLUMN ariel_property.username; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.username IS 'User who created this record';
--
-- Name: COLUMN ariel_property.creationdate; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.creationdate IS 'When this record was created';
--
-- Name: COLUMN ariel_property.editdate; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.editdate IS 'When this record was last edited';
--
-- Name: ariel_property ariel_property_pkey; Type: CONSTRAINT; Schema: public; Owner: qradar
--
ALTER TABLE ONLY public.ariel_property
ADD CONSTRAINT ariel_property_pkey PRIMARY KEY (id);
--
-- Name: ariel_property ariel_property_propertyname_key; Type: CONSTRAINT; Schema: public; Owner: qradar
--
ALTER TABLE ONLY public.ariel_property
ADD CONSTRAINT ariel_property_propertyname_key UNIQUE (propertyname);
--
-- Name: ariel_property ariel_property_sequenceid_key; Type: CONSTRAINT; Schema: public; Owner: qradar
--
ALTER TABLE ONLY public.ariel_property
ADD CONSTRAINT ariel_property_sequenceid_key UNIQUE (sequenceid);
--
-- PostgreSQL database dump complete
--
-----Original Message-----
From: Daniel Gustafsson <daniel@yesql.se>
Sent: Wednesday, November 18, 2020 2:50 PM
To: Jonathan Tauman <JonathanT@qmasters.co>
Cc: pgsql-bugs@postgresql.org; Lior Dahan <liord@qmasters.co>
Subject: Re: Issues with pg_dump command
> On 18 Nov 2020, at 13:34, Jonathan Tauman <JonathanT@qmasters.co> wrote:
> Hi, I was running into a problem with the pg_dump command, I am
> dumping several tables into a file but one table’s data does not appear in the file, can you please assist me with
thisissue?
You need to provide a lot more details in order for anyone to be able to help.
How are you running pg_dump, which version is it and what does the tables look like etc?
If you can create a minimal reproducer which shows the bug then that helps a lot in troubleshooting.
cheers ./daniel
* I forgot to mention the OS is CentOS 6
-----Original Message-----
From: Lior Dahan
Sent: Wednesday, November 18, 2020 3:58 PM
To: Daniel Gustafsson <daniel@yesql.se>
Cc: pgsql-bugs@postgresql.org; Jonathan Tauman <JonathanT@qmasters.co>
Subject: RE: Issues with pg_dump command
We are calling pg_dump for a 9.6.15 PostgreSQL installation (on QRadar).
Here is the call to the command:
pg_dump -U qradar -d qradar -a --disable-triggers -t ariel_property
The dump output looks like this:
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.6.15
-- Dumped by pg_dump version 9.6.15
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;
--
-- Data for Name: ariel_property; Type: TABLE DATA; Schema: public; Owner: qrada
r
--
SET SESSION AUTHORIZATION DEFAULT;
ALTER TABLE public.ariel_property DISABLE TRIGGER ALL;
COPY public.ariel_property (id, propertyname, description, database, username, c
reationdate, editdate, sequenceid, description_id, tenant_id) FROM stdin;
\.
ALTER TABLE public.ariel_property ENABLE TRIGGER ALL;
--
-- PostgreSQL database dump complete
--
The schema dump looks like this:
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.6.15
-- Dumped by pg_dump version 9.6.15
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;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: ariel_property; Type: TABLE; Schema: public; Owner: qradar
--
CREATE TABLE public.ariel_property (
id character varying(255) NOT NULL,
propertyname character varying(255) NOT NULL,
description character varying(1275) NOT NULL,
database character varying(255) NOT NULL,
username character varying(255) NOT NULL,
creationdate bigint NOT NULL,
editdate bigint NOT NULL,
sequenceid bigint DEFAULT nextval('public.ariel_property_seq'::regclass) NOT NULL,
description_id character varying(1275),
tenant_id integer DEFAULT 0 NOT NULL );
ALTER TABLE public.ariel_property OWNER TO qradar;
--
-- Name: COLUMN ariel_property.id; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.id IS 'code generated UUID';
--
-- Name: COLUMN ariel_property.propertyname; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.propertyname IS 'Name of this property. Appears in UI';
--
-- Name: COLUMN ariel_property.database; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.database IS 'Database this property is for (events, flows)';
--
-- Name: COLUMN ariel_property.username; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.username IS 'User who created this record';
--
-- Name: COLUMN ariel_property.creationdate; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.creationdate IS 'When this record was created';
--
-- Name: COLUMN ariel_property.editdate; Type: COMMENT; Schema: public; Owner: qradar
--
COMMENT ON COLUMN public.ariel_property.editdate IS 'When this record was last edited';
--
-- Name: ariel_property ariel_property_pkey; Type: CONSTRAINT; Schema: public; Owner: qradar
--
ALTER TABLE ONLY public.ariel_property
ADD CONSTRAINT ariel_property_pkey PRIMARY KEY (id);
--
-- Name: ariel_property ariel_property_propertyname_key; Type: CONSTRAINT; Schema: public; Owner: qradar
--
ALTER TABLE ONLY public.ariel_property
ADD CONSTRAINT ariel_property_propertyname_key UNIQUE (propertyname);
--
-- Name: ariel_property ariel_property_sequenceid_key; Type: CONSTRAINT; Schema: public; Owner: qradar
--
ALTER TABLE ONLY public.ariel_property
ADD CONSTRAINT ariel_property_sequenceid_key UNIQUE (sequenceid);
--
-- PostgreSQL database dump complete
--
-----Original Message-----
From: Daniel Gustafsson <daniel@yesql.se>
Sent: Wednesday, November 18, 2020 2:50 PM
To: Jonathan Tauman <JonathanT@qmasters.co>
Cc: pgsql-bugs@postgresql.org; Lior Dahan <liord@qmasters.co>
Subject: Re: Issues with pg_dump command
> On 18 Nov 2020, at 13:34, Jonathan Tauman <JonathanT@qmasters.co> wrote:
> Hi, I was running into a problem with the pg_dump command, I am
> dumping several tables into a file but one table’s data does not appear in the file, can you please assist me with
thisissue?
You need to provide a lot more details in order for anyone to be able to help.
How are you running pg_dump, which version is it and what does the tables look like etc?
If you can create a minimal reproducer which shows the bug then that helps a lot in troubleshooting.
cheers ./daniel
On Wed, Nov 18, 2020 at 01:57:46PM +0000, Lior Dahan wrote: > We are calling pg_dump for a 9.6.15 PostgreSQL installation (on QRadar). > > Here is the call to the command: > pg_dump -U qradar -d qradar -a --disable-triggers -t ariel_property > > The dump output looks like this: Based on the triggers, I wonder if ariel_property is the parent of a partitioned table, and there is nothing in the parent table. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Is there a way we can tell which tables are under this parent one? Perhaps we can dump them instead. -----Original Message----- From: Bruce Momjian <bruce@momjian.us> Sent: Wednesday, November 18, 2020 4:16 PM To: Lior Dahan <liord@qmasters.co> Cc: Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@postgresql.org; Jonathan Tauman <JonathanT@qmasters.co> Subject: Re: Issues with pg_dump command On Wed, Nov 18, 2020 at 01:57:46PM +0000, Lior Dahan wrote: > We are calling pg_dump for a 9.6.15 PostgreSQL installation (on QRadar). > > Here is the call to the command: > pg_dump -U qradar -d qradar -a --disable-triggers -t ariel_property > > The dump output looks like this: Based on the triggers, I wonder if ariel_property is the parent of a partitioned table, and there is nothing in the parenttable. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
On Wed, Nov 18, 2020 at 02:37:50PM +0000, Lior Dahan wrote:
> Is there a way we can tell which tables are under this parent one? Perhaps we can dump them instead.
Well, in psql, \d will show you the children:
CREATE TABLE parent (x int);
CREATE TABLE child (y int) INHERITS (parent);
\d parent
Table "public.parent"
Column | Type | Modifiers
--------+---------+-----------
x | integer |
--> Number of child tables: 1 (Use \d+ to list them.)
test=> \d+ parent
Table "public.parent"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
x | integer | | plain | |
--> Child tables: child
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On Wed, Nov 18, 2020 at 7:44 AM Lior Dahan <liord@qmasters.co> wrote:
Is there a way we can tell which tables are under this parent one? Perhaps we can dump them instead.
The convention here is to inline or bottom-post.
If you don't use declarative partitioning then the specifics of the partitioning are buried inside the custom trigger functions that you wrote. If you dump triggers, they will show in the pg_dump output, otherwise you will have to investigate using other means.
David J.
On Wed, Nov 18, 2020 at 07:52:31AM -0700, David G. Johnston wrote: > On Wed, Nov 18, 2020 at 7:44 AM Lior Dahan <liord@qmasters.co> wrote: > > Is there a way we can tell which tables are under this parent one? Perhaps > we can dump them instead. > > > The convention here is to inline or bottom-post. > > If you don't use declarative partitioning then the specifics of the > partitioning are buried inside the custom trigger functions that you wrote. If > you dump triggers, they will show in the pg_dump output, otherwise you will > have to investigate using other means. He is on PG 9.6.15, which doesn't support declarative partitioning, so it must be trigger-based. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee