PgSqlDump don't save INDEX Creation

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Feneck91
Posts: 46
Joined: Mon 12 Aug 2013 13:52

PgSqlDump don't save INDEX Creation

Post by Feneck91 » Fri 09 Mar 2018 12:44

I use PgSqlDump to backup my database (EntityFramework 6.2.0).
This is an example of my source code :

Code: Select all

PgSqlConnection conn = <my PgSqlConnection>;
conn.Open();
PgSqlDump pgSqlDump = new PgSqlDump();
pgSqlDump.Connection = conn;
pgSqlDump.IncludeDrop = false;
pgSqlDump.IncludeBlob = true;
pgSqlDump.GenerateHeader = true;
pgSqlDump.IncludeUsers = false;
pgSqlDump.Mode = Devart.Common.DumpMode.All;
pgSqlDump.Backup(_strFilePath);
It create the Table structures with PRIMARY KEY like :
CREATE TABLE public."DMGeneral"
(
"GeneralID" bigint,
"SchemaVersion" smallint NOT NULL,
"DataVersion" smallint NOT NULL,
);
...
...
ALTER TABLE ONLY public."DMGeneral" ADD CONSTRAINT "DMGeneral_pkey" PRIMARY KEY ("GeneralID");
But no ForeignKeys created by Entity Framework and INDEXES are backup like (another table that contains forreign key + real index) :
-- Table DMCommon
CREATE INDEX IF NOT EXISTS "IX_DMCommon_FKCodeDetails"
ON public."DMCommon" USING btree
("FKCodeDetails")
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS "IX_DMCommon_FKFournitureID"
ON public."DMCommon" USING btree
("FKFournitureID")
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS "IX_DMCommon_Nom"
ON public."DMCommon" USING btree
("Nom" COLLATE pg_catalog."default")
TABLESPACE pg_default;
So backup the database and restore it lose ALL INDEX !
Is it normal ?

I give you the request to get all indexes :

Code: Select all

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind
      WHEN 'r' THEN 'table'
      WHEN 'v' THEN 'view'
      WHEN 'i' THEN 'index'
      WHEN 'S' THEN 'sequence'
      WHEN 's' THEN 'special'
  END as "Type",
  u.usename as "Owner",
  c2.relname as "Table",
  idx.indisunique as "Unique",
  idx.indisprimary as "Primary",
  am.amname as "TypeIndex",
  idx.indkey As "IndexKeys",
  ARRAY( SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
         FROM generate_subscripts(idx.indkey, 1) as k
         ORDER BY k
       ) as IndexKeyNames
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_index         idx ON idx.indexrelid = c.oid
     JOIN pg_catalog.pg_class         c2  ON c2.oid         = idx.indrelid
     JOIN pg_catalog.pg_class         c3  ON c3.oid         = idx.indexrelid
     JOIN pg_catalog.pg_am            am  ON am.oid         = c3.relam
     LEFT JOIN pg_catalog.pg_user      u  ON u.usesysid     = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n  ON n.oid          = c.relnamespace
WHERE c.relkind IN ('i','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY 1,2;

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlDump don't save INDEX Creation

Post by Pinturiccio » Thu 15 Mar 2018 17:31

Sorry for the late response.

PgSqlDump has the PgSqlDumpObjects property. By default it contains the following value:

Code: Select all

Devart.Data.PostgreSql.PgSqlDumpObjects.Tables | Devart.Data.PostgreSql.PgSqlDumpObjects.Constraints
You can specify the following value:

Code: Select all

Devart.Data.PostgreSql.PgSqlDumpObjects.Tables | Devart.Data.PostgreSql.PgSqlDumpObjects.Constraints | PgSqlDumpObjects.Indexes
or PgSqlDumpObjects.All to add all objects to backup. For more information, please refer to https://www.devart.com/dotconnect/postg ... jects.html

Post Reply