title: postgreSQL dump and restore partitioned table (w/ insert trigger) from one server to another
body: <p>Trying to dump a partitioned table from one server to another in PostgreSQL (9.4.5).  Fairly new to postgres, and inherited the project, please let me know if more background info is needed.</p>
  
  <pre><code>dbname=&gt; SELECT COUNT (id) FROM parent_table_to_copy;
  count  
  --------
  499992
  (1 row)
  </code></pre>
  
  <p><code>parent_table_to_copy</code> has a trigger:</p>
  
  <pre><code>parent_table_to_copy_trigger BEFORE INSERT ON parent_table_to_copy FOR EACH ROW EXECUTE PROCEDURE parent_table_to_copy_function()
  </code></pre>
  
  <p>Maybe not important, but the function is a series of <code>ELSIF</code>'s that partition <code>parent_table_to_copy</code> into 26 children alphabetically by the first letter for the record for the <code>name</code> column.  For example if I wanted all the records whose <code>name</code> starts with "A" from <code>parent_table_to_copy</code>, a subset is returned with:</p>
  
  <pre><code>dbname=&gt; SELECT COUNT(id) FROM child_table_a;
  count 
  -------
  34064
  (1 row)
  </code></pre>
  
  <p>Now for the dump/restore into our backup of the database housed on <strong>hostname2</strong>
  I used <code>--inserts</code> because of the <em>insert</em> trigger described above, didn't think the default<code>COPY</code> statement would work.</p>
  
  <pre><code>pg_dump --host=hostname1 --username=user --table="parent_table_to_copy" --inserts --data-only --file=dump_out.sql dbname
  </code></pre>
  
  <p>Contents of <code>dump_out.sql</code>  However there doesn't seem to be any <code>INSERT</code> commands.  If I take out <code>--inserts</code> then a <code>COPY</code> command appears.</p>
  
  <pre><code>--
  -- PostgreSQL database dump
  --
  
  SET statement_timeout = 0;
  SET lock_timeout = 0;
  SET client_encoding = 'UTF8';
  SET standard_conforming_strings = on;
  SET check_function_bodies = false;
  SET client_min_messages = warning;
  
  SET search_path = public, pg_catalog;
  
  --
  -- Data for Name: parent_table_to_copy; Type: TABLE DATA; Schema: public; Owner: user
  --
  
  
  --
  -- PostgreSQL database dump complete
  --
  </code></pre>
  
  <p>Finally, <code>psql -U user -h hostname2 -d dbname -f dump_out.sql</code> just returns seven <code>SET</code> commands and when I <code>SELECT</code>  from <strong>hostname2</strong> there are 0 records.</p>
  
  <p>Any insight would be greatly appreciated, thank you for reading. -Kevin</p>
  
  <blockquote>
    <p>Edit Below ~ Create table statement included</p>
  </blockquote>
  
  <pre><code>CREATE TABLE IF NOT EXISTS parent_table_to_copy
  (
  id INTEGER NOT NULL,
  name CHARACTER VARYING(25) NOT NULL,
  vartype CHARACTER VARYING(26) NOT NULL,
  var2type NUMERIC NOT NULL
  );
  
  CREATE TABLE child_table_a
  ( CONSTRAINT child_table_a_id_fk FOREIGN KEY (id) REFERENCES some_table (id) MATCH FULL ON DELETE CASCADE,
  CONSTRAINT child_table_a_gene_name_fk FOREIGN KEY (name) REFERENCES some_other_table (name) MATCH FULL ON DELETE CASCADE,
  CHECK (name like 'A%')
  ) INHERITS (parent_table_to_copy);
  CREATE INDEX child_type_a ON child_table_a (id,name,vartype);
  </code></pre>