PgsqlDump
PgsqlDump
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
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
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.
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.
Backup Of System table of Postgre i.e pg_largeObject
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
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
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.
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.
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
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
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:
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;
Ya. i have set IncludeBlob Property before calling pgSqldump.Backup() Method.
Code is
And Generated script
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
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()
- -- 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
PgSqlDump
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:
Or any solution to resolve this?
Thanks to guide me in right direction.
-Vimmi
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)
Or any solution to resolve this?
Thanks to guide me in right direction.
-Vimmi
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();
pgAqlDump
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 :
Restore code:
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
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")
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()
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
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.
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.
pgSqldump
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.
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.
Re: PgsqlDump
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.