Thread: Issues with pg_dump command

Issues with pg_dump command

From
Jonathan Tauman
Date:

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

 

 

logo1withqmasters

Jonathan Tauman | 

M: +972-58-6333-805|

jonathant@qmasters.co

www.qmasters.co

 

 

Attachment

Re: Issues with pg_dump command

From
Daniel Gustafsson
Date:
> 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


RE: Issues with pg_dump command

From
Lior Dahan
Date:
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

RE: Issues with pg_dump command

From
Lior Dahan
Date:
* 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

Re: Issues with pg_dump command

From
Bruce Momjian
Date:
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




RE: Issues with pg_dump command

From
Lior Dahan
Date:
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


Re: Issues with pg_dump command

From
Bruce Momjian
Date:
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




Re: Issues with pg_dump command

From
"David G. Johnston"
Date:
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.

Re: Issues with pg_dump command

From
Bruce Momjian
Date:
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