PgsqlDump

vimmi · Post by **vimmi** » Thu 16 Sep 2010 09:48

Hello,

I have installed dotConnect Express for PostgreSQL 4.50. And tried to get backup of Databse for that i am using pgSQLDump class. Here i want to get backup of pg_largeobjecttable also. So what should i do? In docimentation i read need to set includeblob property to TRUE. i tried with this also but here created tables and data of those tables are coming in Script file but i am not able to get data of pg_largeobject table which is default table of database.

I am working in vb.net and
tried code is

*************************************************************

Dim clspgSqlDump = New PgSqlDump(Database.Connection)
clspgSqlDump.IncludeBlob = True
clspgSqlDump.Backup(Application.StartupPath & "\backup\backupfile.backup")

*************************************************************

Can you suggest what can i do to get data of pg_largeobject table?

Thanks in adv.

-Vimmi

Shalex · Post by **Shalex** » Fri 17 Sep 2010 12:48

1. dotConnect for PostgreSQL Express Edition doesn't include the PgSqlDump class. It is available only in Professional Edition: http://www.devart.com/dotconnect/postgr ... tions.html.
2. The versions of dotConnect for PostgreSQL which are prior to 4.95.152 include field values of ByteA type into database dump when PgSqlDump.IncludeBlob=true.
This behaviuor was changed in 4.95.152: PgSqlDump.IncludeBlob=true enables dump of large objects, associated with Oid filed values of dumped tables. This functionality is described in current documentation.
Starting from the next build of dotConnect for PostgreSQL, PgSqlDump.IncludeDrop=true will also generate the statement for removing Large Object.

vimmi · Post by **vimmi** » Mon 20 Sep 2010 05:13

Thanks for reply.

Ya that is fine.pgsqlDump class is taken backup of byteA object of table which is created by us but i want to take back up of pg_largeobject(System table of postgre), for that i have tried same code wriiten in last post but it did not work.

Can you suggest me which property i have to set to get backup of system table pg_largeObject

Thanks in adv and waiting for your reply
- Vimmi

Shalex · Post by **Shalex** » Mon 20 Sep 2010 07:38

1. Please tell us the version (x.xx.xxx) and edition of your current dotConnect for PostgreSQL. You can find it via the Tools > PostgreSQL > About menu of Visual Studio.
2. You can find an example of using the PgSqlDump class in its description. Please set pgSqlDump.IncludeBlob=true before calling pgSqlDump.Backup() additionally in the code from the sample.

vimmi · Post by **vimmi** » Tue 21 Sep 2010 06:29

Hello Shalex,

Version of dotConnect for PostgreSQL is "4.95.152.0". Ya. i have refered this documention as well as sample examples.

Can you tell me have you implemented concept to take backup of Postgre systemtable 'pg_largeObject'?

By setting pgSqlDump.IncludeBlob=true, i am able to take backup of bytea largeObjects fields of tables but those tables are created by us, its not a systemtable. I am talking about that backup of largeObject of systemtable i.e 'pg_largeObject' is not working.

Thanks for quick reply.
-Vimmi

Shalex · Post by **Shalex** » Tue 21 Sep 2010 09:16

Backup of a large object in dotConnect for PostgreSQL is data and SQL statements for creating lo, writing data into it, closing lo, and removing lo. SQL statements contain calls of PostgreSQL built-in functions.

For example, backup of a large object with the IncludeDrop=true and IncludeBlob=true options generates the following script:

Code: Select all

DROP TABLE public.oid_table;
CREATE TABLE public.oid_table
(
  oid_column oid
);
INSERT INTO public.oid_table VALUES (1646119);
BEGIN;
SELECT lo_unlink(1646119);
SELECT lo_open(lo_create(1646119), 131072);
SELECT lowrite(0, 'Hello, world!');
SELECT lo_close(0);
COMMIT;

vimmi · Post by **vimmi** » Fri 24 Sep 2010 05:46

Ya. i have set IncludeBlob Property before calling pgSqldump.Backup() Method.

Code is

Code: Select all

clspgSqlDump = New PgSqlDump(Database.Connection)
clspgSqlDump.IncludeBlob = True
clspgSqlDump.Backup()
Dim stream As StreamWriter = New StreamWriter(Application.StartupPath & "\backup\sqldump1.dmp")

stream.WriteLine(clspgSqlDump.DumpText)
stream.Close()
Database.ConnectionClose()

And Generated script

-- dotConnect for PostgreSQL 4.95.152.0
-- Server version: PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit
-- Script date 24/09/2010 11:01:28 AM
-- Server: localhost
-- Database: restoredb

SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET default_tablespace = '';
SET default_with_oids = false;

CREATE TABLE public.extensions
(
extensionid integer,
extension varchar(10),
iconsmall bytea,
iconmedium bytea,
iconlarge bytea,
creationdate timestamp(6)
);

INSERT INTO public.extensions VALUES (206, '.pdf',

CREATE TABLE public.settings
(
displayname text,
username text,
"password" text,
thumbnailthreads integer,
ismsgexpirable boolean,
msgexpirabledays integer,
ismsgreminder boolean,
msghours integer,
ismsgrecurring boolean,
msgrecurringdays integer,
notificationexpirabledays integer,
isnotificationrecurringreminder boolean,
notificationrecurringdays integer,
brightness integer,
contrast integer,
isduplex boolean,
ishideui boolean,
isnegative boolean,
isremoveblackborders boolean,
isautodeskew boolean,
isremoveisolateddots boolean,
rotate90 boolean,
rotate180 boolean,
categoryid integer,
licensedata text
);

INSERT INTO public.settings VALUES ('Administrator', 'admin', 'admin', 2, 'True', 7, 'True', 1, 'True', 7, 10, 'True', 10, 500, 500, 'False', 'False', 'False', 'False', 'False', 'False', 'False', 'False', 0, 'HjszZdW+qJwJySTuZdFqVL98gisaXNJwpcfSrveduHA=');
INSERT INTO public.settings VALUES ('Administrator', 'admin', 'admin', 2, 'True', 7, 'True', 1, 'True', 7, 10, 'True', 10, 500, 500, 'False', 'False', 'False', 'False', 'False', 'False', 'False', 'False', 0, 'HjszZdW+qJwJySTuZdFqVL98gisaXNJwpcfSrveduHA=');

CREATE TABLE public.supportedextensions
(
extensionid integer,
extension text,
"default" boolean
);

CREATE SEQUENCE public.supportedextension_extensionid_seq
START 1;
ALTER SEQUENCE public.supportedextension_extensionid_seq OWNER TO postgres;

ALTER SEQUENCE public.supportedextension_extensionid_seq OWNED BY public.supportedextensions.extensionid;

ALTER TABLE public.extensions ALTER COLUMN extensionid
SET DEFAULT nextval('extensions_extensionid_seq'::regclass);
ALTER TABLE public.extensions ALTER COLUMN extension
SET DEFAULT NULL::character varying;
ALTER TABLE public.extensions ALTER COLUMN creationdate
SET DEFAULT NULL::timestamp without time zone;
ALTER TABLE public.supportedextensions ALTER COLUMN extensionid
SET DEFAULT nextval('supportedextension_extensionid_seq'::regclass);

ALTER TABLE ONLY public.extensions ADD CONSTRAINT extensions_pkey PRIMARY KEY (extensionid);
ALTER TABLE ONLY public.supportedextensions ADD CONSTRAINT supportedextension_pkey PRIMARY KEY (extensionid);

But here i did not found script of pg_largeObject (Creation of table, insertion of records)
although in this connection table 'pg_largeObject' of database 'restoredb' has around 500 records.

Can you suggest me now what i can do to get back up of table pg_largeObject(i.e System table)?

Thanks & Regards,
-Vimmi

Shalex · Post by **Shalex** » Mon 27 Sep 2010 16:30

dotConnect for PostgreSQL creates backup only for those records from pg_largeObject whose OIDs are used in the dumped user's tables (not system). Why do you need to back up records from pg_largeObject if they are not referenced via OIDs from your user defined database objects?

vimmi · Post by **vimmi** » Tue 28 Sep 2010 05:42

Hi Shalex,

Thanks for reply.
I have tried To get Backup of Large_Object. Ya its worked now. It took backup of default table oid links. And in Backup file i can see Script of largeObject table.

But now i faced another problem and this problem is with table in which we have created Sequence. We have used serial datatype for auto increment. But when i restore backup of those tables then at restoring time i got error

Error:

Devart.Data.PostgreSql.PgSqlException: relation "files_fileid_seq" does not exist
at Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
at Devart.Data.PostgreSql.PgSqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Devart.Common.DbScript.ExecuteSqlStatement(SqlStatement sqlStatement)
at Devart.Common.DbScript.b(SqlStatement A_0)
at Devart.Common.SqlStatement.Execute()
at Devart.Common.DbScript.ExecuteNext(IDataReader& reader)
at Devart.Common.DbScript.Execute()
at Devart.Data.PostgreSql.PgSqlDump.Restore(TextReader reader)
at Devart.Data.PostgreSql.PgSqlDump.Restore(String fileName)

can you suggest me which property need to be set to resolve this Error?
Or any solution to resolve this?

Thanks to guide me in right direction.

-Vimmi

Shalex · Post by **Shalex** » Wed 29 Sep 2010 16:04

Please open the dumped file in text editor and try to localize the problem. Which incorrect SQL was generated by the Devart.Data.PostgreSql.PgSqlDump class? I cannot reproduce the problem in our test environment with the 4.95.170 version of dotConnect for PosgreSQL. Please tell your currect version (Tools > PostgreSQL > About), the DDL/DML script for your table with the serial column, the code your are using for backup/restore. I have tried this code:

Code: Select all

CREATE TABLE seqtbl
(
  id serial NOT NULL,
  data character varying(20)
);
insert into seqtbl(data) values ('some data');

Code: Select all

            PgSqlConnection conn = new PgSqlConnection("user id = ***;password=***;host=db;port=5437;database=postgres;");
            conn.Open();

            PgSqlDump pgSqlDump = new PgSqlDump();
            pgSqlDump.Connection = conn;
            pgSqlDump.Schema = "public";
            pgSqlDump.Tables = "seqtbl";
            pgSqlDump.IncludeDrop = true;
            pgSqlDump.Backup();

            StreamWriter stream = new StreamWriter("d:\sqldump1.dmp");
            stream.WriteLine(pgSqlDump.DumpText);
            stream.Close();

            pgSqlDump.Restore("d:\sqldump1.dmp");
            conn.Close();

vimmi · Post by **vimmi** » Thu 30 Sep 2010 09:44

Thanks to explain me by code.

Ya. i tried same code it works. But actually i want to copy Data from one database to another or I want to get Backup of whole database from one system And restore this backup into Another system. When i am doing this at that time i got error.

Backup code :

Code: Select all

 Dim connBackUp As New PgSqlConnection("User Id=postgres;Password=****;Host=localhost;Port=5432;Database=backupDB")
            connBackUp.Open()
            Dim clspgSqlDump = New PgSqlDump()
            clspgSqlDump.Connection = connBackUp
            clspgSqlDump.Schema = "public"
            clspgSqlDump.Tables = "files"
            clspgSqlDump.IncludeBlob = True
            clspgSqlDump.Backup(Application.StartupPath & "\backup\sqldump1.dmp")

Restore code:

Code: Select all

 Dim connRestore As New PgSqlConnection("User Id=postgres;Password=****;Host=localhost;Port=5432;Database=restoreDB")
            connRestore.Open()
            clspgSqlDump.Connection = connRestore
            clspgSqlDump.Restore(Application.StartupPath & "\backup\sqldumpFiles.dmp")
            connRestore.Close()

Here i want to take backup of Databse backupDB and restore into Databse restoreDb, and here files table haing Datatype serial at that time i got Sequence does not exist error.

Can you tell me it is right way to copy table structure and data from one Database to another? Or do you have another approach to do this?

Thanks
Vimmi

Shalex · Post by **Shalex** » Mon 04 Oct 2010 15:10

This should work. I cannot reproduce the error using your code with different database for restoring as well. Please send me (alexsh at devart*com) the following information:
1) the DDL (/DML) script of your "files" table;
2) the dump file generated by our provider;
3) the version of dotConnect for PostgreSQL you are using (Tools > PostgreSQL > About);
4) the version of your PostgreSQL Server.

vimmi · Post by **vimmi** » Tue 05 Oct 2010 05:49

Hi Shalex,

Thanks for reply.
Ya I have sent you Script file of my Database and Script file generated by your provider into previous mention Email ID.

Just Check with those files and let me know feedback.

Here version of dotConnect for PostgreSQL - 4.95.152.0

And

version of your PostgreSQL Server (pgAdminIII - PostgreSql tools)- 1.10.

Waiting for reply. Thanks.

Shalex · Post by **Shalex** » Wed 06 Oct 2010 13:42

We have answered you by e-mail (the reason of the problem is that the create sequence statement is not present in the dump).

Shalex · Post by **Shalex** » Wed 25 Sep 2013 12:22

The bug with backing up the serial columns with PgSqlDump is fixed. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

Devart Forums

PgsqlDump

PgsqlDump

Backup Of System table of Postgre i.e pg_largeObject

PgSqlDump

pgAqlDump

pgSqldump

Re: PgsqlDump