Issues with pg_dump command

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Issues with pg_dump command

Jonathan Tauman

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|

[hidden email]

www.qmasters.co

 

 

Reply | Threaded
Open this post in threaded view
|

Re: Issues with pg_dump command

Daniel Gustafsson
> On 18 Nov 2020, at 13:34, Jonathan Tauman <[hidden email]> 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 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

Reply | Threaded
Open this post in threaded view
|

RE: Issues with pg_dump command

Lior Dahan
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 <[hidden email]>
Sent: Wednesday, November 18, 2020 2:50 PM
To: Jonathan Tauman <[hidden email]>
Cc: [hidden email]; Lior Dahan <[hidden email]>
Subject: Re: Issues with pg_dump command

> On 18 Nov 2020, at 13:34, Jonathan Tauman <[hidden email]> 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 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
Reply | Threaded
Open this post in threaded view
|

RE: Issues with pg_dump command

Lior Dahan
* 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 <[hidden email]>
Cc: [hidden email]; Jonathan Tauman <[hidden email]>
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 <[hidden email]>
Sent: Wednesday, November 18, 2020 2:50 PM
To: Jonathan Tauman <[hidden email]>
Cc: [hidden email]; Lior Dahan <[hidden email]>
Subject: Re: Issues with pg_dump command

> On 18 Nov 2020, at 13:34, Jonathan Tauman <[hidden email]> 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 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
Reply | Threaded
Open this post in threaded view
|

Re: Issues with pg_dump command

Bruce Momjian
In reply to this post by Lior Dahan
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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee



Reply | Threaded
Open this post in threaded view
|

RE: Issues with pg_dump command

Lior Dahan
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 <[hidden email]>
Sent: Wednesday, November 18, 2020 4:16 PM
To: Lior Dahan <[hidden email]>
Cc: Daniel Gustafsson <[hidden email]>; [hidden email]; Jonathan Tauman <[hidden email]>
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 parent table.

--
  Bruce Momjian  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Reply | Threaded
Open this post in threaded view
|

Re: Issues with pg_dump command

Bruce Momjian
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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee



Reply | Threaded
Open this post in threaded view
|

Re: Issues with pg_dump command

David G Johnston
In reply to this post by Lior Dahan
On Wed, Nov 18, 2020 at 7:44 AM Lior Dahan <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Issues with pg_dump command

Bruce Momjian
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 <[hidden email]> 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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee