Questions tagged [dump]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
71 votes
9 answers
125k views

Export Postgres table as json

Is there a way to export postgres table data as json to a file? I need the output to be line by line, like: {'id':1,'name':'David'} {'id':2,'name':'James'} ... EDIT: postgres version: 9.3.4
AliBZ's user avatar
  • 1,797
54 votes
1 answer
79k views

postgresql: how do I dump and restore roles for a cluster?

Where are roles stored in a cluster, and how do I dump them? I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors: psql:mydump.sql:3621: ERROR: role "...
Rob Bednark's user avatar
  • 2,163
49 votes
1 answer
78k views

Dump only the Stored Procedures in MySQL

I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
nakhli's user avatar
  • 733
35 votes
4 answers
49k views

how to track progress of a large postgres dump

Is there a way to see the progress of a pg_dump operation on a big db (ie > 1GB)? adding the -v option just dumps text on the screen, but doesn't give me much meaningful tracking information.
abbood's user avatar
  • 453
14 votes
5 answers
47k views

MySQL server has gone away obstructing import of large dumps

I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a ERROR 2006 (HY000) at line 7758: MySQL server ...
naxoc's user avatar
  • 241
10 votes
4 answers
40k views

Dump Oracle database into a textual SQL script

I am thinking on what mysqldump or pg_dump are doing. So, they dump an active database into a textual file containing the SQL queries, which reproduce this whole database. In MySQL and in PostgreSQL, ...
peterh's user avatar
  • 2,077
9 votes
1 answer
777 views

2019 Enterprise - NOLOCK without NOLOCK, page latch time-out on tempdb and finally a dump [closed]

We have a very strange problem with a new SQL Server 2019 on 2 new physical machines: Infrastructure: Starting a new installation of SQL Server 2019 Enterprise (15.0.2000.5 / X64 on Windows Server ...
SQLpro's user avatar
  • 509
8 votes
2 answers
20k views

DROP PROCEDURE IF EXISTS not included in mysqldump

I'm dumping my stored procedures only using the following command: mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql but the ...
nakhli's user avatar
  • 733
7 votes
1 answer
7k views

How to change temporary directory for pg_dump?

I'm running out of space from my hard drive which has a 18GB PostgreSQL database. When I try pg_dump to a different drive, PostgreSQL creates temporary files on the disk it's dumping from, so I run ...
kissgyorgy's user avatar
7 votes
2 answers
552 views

Exporting database Oracle 7.3 on Windows NT 3.51

How to take dump files on Oracle7.3 running on Windows NT 3.51? Should you stand in the Orant/bin folder and run one of the programs there like> exp73 SYSTEM/password@[dbinstance] FULL=Y LOG=EXPORT....
Chris_45's user avatar
  • 211
7 votes
3 answers
24k views

Import Oracle full dump file to fresh Oracle installation

I am importing a Oracle dump fill created on a AIX Oracle 9.2 installation with a Full Export option to a Windows Oracle 10.2.0.4 on Windows 2008 R2 x64. I want to import the file to a clean ...
Alexandre Sousa's user avatar
6 votes
1 answer
4k views

pg_restore warning column X has type unknown

I'm doing a dump from PostgreSQL 9.1 and restoring on PostgreSQL 9.4 and getting this warning pg_restore: WARNING: column "foobar" has type "unknown" DETAIL: Proceeding with relation creation ...
Phill Pafford's user avatar
6 votes
2 answers
452 views

SQL Server generating sporadic Memory Dumps

We're running a 2014 SQL Server. For the past couple of years, the server generates memory dumps with no rhyme or reason, every 2-7 days, locking up most processes, and forcing us to restart the SQL ...
Depth of Field's user avatar
6 votes
1 answer
3k views

PostgreSQL auto-vacuum: "skipped frozen" pages causing massive bloating

I am having a problem with a table I am using for a forum where people can add their products. Everytime a user loads a page, the "online timestamp" of the user gets updated (a PHP line prevents it ...
Mike Rocky's user avatar
6 votes
1 answer
8k views

Fastest way to extract full table in Postgres

I'm trying to dump a table with 50M records to a file, and my goal is to reduce the time in which this action is performed. I usually use the COPY metrics TO 'metrics.csv' DELIMITER ',' CSV; This ...
Imanol Y.'s user avatar
  • 785
6 votes
1 answer
7k views

Backup a database with a huge number of tables

Is there a way to backup a PostgreSQL database with a huge number of tables? An attempt with pg_dump for a database of about 28000 tables resulted in the following error message: pg_dump: WARNING: ...
krlmlr's user avatar
  • 361
5 votes
1 answer
4k views

Dump PostgreSQL schema with foreign keys and referenced data

The official PostgreSQL documentation states that pg_dump can use the --schema=SCHEMA option to selectively dump a given schema. However according to the same documentation (read Note on --schema=...
Unode's user avatar
  • 151
4 votes
2 answers
14k views

What to look for in bad pg_dump log

We want to programmatically detect errors in cron scheduled pg_dumps. Apart from cheking whether or not the log file ends in "pg_dump: saving database definition": What other tell-tale string can I ...
Tulains Córdova's user avatar
4 votes
1 answer
4k views

Parsing COPY's binary format to access a tsrange

How is tsrange stored in binary? For example create table CREATE TABLE public.test (t tsrange); INSERT INTO test VALUES ('[2010-01-01 14:30, 2010-01-01 15:30)'); INSERT INTO test VALUES ('[2011-01-...
eri's user avatar
  • 143
4 votes
4 answers
14k views

Why is Microsoft SQL Server creating a ton of dump files?

How to determine what is causing Microsoft SQL Server to create dump files over and over taking up 170 GB of disk space? How do I troubleshoot what is causing the dumps? The path of the dump files is ...
Tanooki Mario's user avatar
4 votes
2 answers
21k views

How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0

I am running following command to dump my database ./expdp username/password@//host:port/database directory=/home/ubuntu/oracle/instantclient_12_2 dumpfile=dumpfilename.dmp content=METADATA_ONLY and ...
Bhaskar Dabhi's user avatar
4 votes
1 answer
2k views

Mongodb incremental oplog Dump not working

I am not able to take an incremental oplog dump. I am not getting any error messages the connection happens fine and also there is a operation recorded(shows up in db.currentOp()), but still nothing ...
Prashanth Kumar B's user avatar
4 votes
2 answers
8k views

How to create a blank version of a PostgreSQL database

I've been using pgAdmin to create a database and test it. I'm at a point now where I need to install this db on different servers. I've been using pg_dump to do this and I've been able to muddle my ...
dot's user avatar
  • 751
4 votes
1 answer
2k views

Why does an sqlite3 .dump and restore result in a smaller database file size?

I have a really naive question about database file sizes. I started out with a 1.2M SQLite database, dumped it to a SQL file, and then restored it to a new database. The restored database file was ...
igal's user avatar
  • 345
4 votes
1 answer
5k views

Database size twice as large after mysql dump and re-import

After exporting and re-importing a mysql database, the size of the database on disk appears to be quadrupling in size. Any ideas why this my be? This is occurring when importing the sql dump right ...
Patrick's user avatar
  • 171
4 votes
2 answers
1k views

An easy way to select one db dump from multiple db MySQL dump

In an urgent situation another person made a backup of all the databases from my webserver to one *.sql file (MySQL dump). Now I have a ~700 Mb file with about 20 different databases. As I only need ...
Oleksandr Berezko's user avatar
3 votes
4 answers
2k views

How to import only a specific database from a .sql file?

I have a file dump.sql with many databases in it: mydb1, mydb2, mydb3, etc. How to import only mydb3 and not the other databases? Won't: mysql> create database mydb3; mysql> use mydb3; mysql> ...
Basj's user avatar
  • 171
3 votes
4 answers
40k views

Opening a .backup file for PostgreSQL

I'm going to warn you that whoever explains this needs to talk to me like I'm five years old, because this is something way, way beyond my level of expertise. In short, I have what I think is a ...
Yikes's user avatar
  • 31
3 votes
3 answers
13k views

How to detect a running mysqldump?

Here are the things I can't change: We got a mySQL myISAM replication running a master and some slaves. One slave is being used for nightly backups using mysqldump. The backups is using heavy locks ...
Sebastian's user avatar
  • 197
3 votes
1 answer
2k views

Is it possible to backup a subset of a database in Microsoft SQL Server?

Using Microsoft SQL Server, is there a way to backup a subset of a production database? I only want the database structure, and a very small amount of transaction data for a few selected tables. I ...
Sai Kishan's user avatar
3 votes
2 answers
6k views

Sequence number shows a lower value after dump file import

I'm using Oracle 11g database in which I have more than 12 schema's consisting of many tables. I've created sequences for primary keys to these tables. I've also created synonyms to refer my ...
SHS's user avatar
  • 152
3 votes
1 answer
1k views

What are the benefits of using XML over CSV files in regards to data dumps? [closed]

Prologue I've noticed that one benefit of using CSV over XML for data dumps is the fact that fewer disk space is used. See minimal working example below. Let's suppose we have the following dataset. ...
rdrg109's user avatar
  • 131
3 votes
1 answer
44 views

Lock database in time

I'm currently working with prototyping some stuff with a dump of a MySQL (5.7) database where much of the data is time dependent. Most of the queries I write use the NOW() function and the results ...
Johan Falkenjack's user avatar
3 votes
1 answer
2k views

pg_dump freezes on windows service application

I have a system running over Tomcat which executes a backup batch job every day, it calls pg_dump.exe from Java and stores its output to an SQL file. When I'm executing Tomcat from the shell, it ...
Timoteo Ponce's user avatar
3 votes
2 answers
4k views

How to restore a .backup file from a single table on postgres?

I have a .backup dump from a single table, but it didn't work restore like a normal backup from an entire db. I'm creating this backup file with the following batch script bin\pg_dump -h localhost -p ...
lucazpinheiro's user avatar
3 votes
1 answer
1k views

Change pg_dump directory output to table name instead of object number

pg_dump -F d will result in multiple <number>.dat.gz files which represent the object number in the database. I wish pg_dump create those files in the format <table name>.dat.gz instead so ...
Nir's user avatar
  • 523
3 votes
2 answers
1k views

I really can't get rid of these conditional statements in mysqldump?

My mysqldump DDL file is filled with tons of this: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `my_trigger`... I've searched to no avail for an option to silence this. ...
Ifedi Okonkwo's user avatar
3 votes
1 answer
3k views

Is it possible to convert a .mysql file to a .sql file?

I am facing an issue with a MySql dump file (i.e mydump.mysql) generated by a Drupal export module. I am trying to import it on a local Drupal installation via Acquia Dev Desktop. When browsing for ...
Jérôme Verstrynge's user avatar
3 votes
2 answers
5k views

Recovering data from SQLite .db File Data

To give a bit of background, I was looking for the occurrence of specific text from within multiple SQLite DB files. I initially tried a simple grep of the text on the multiple files and grep found ...
MSF004's user avatar
  • 133
3 votes
1 answer
287 views

MySQL - Partially restore binary log

I have restored a dump into MySQL. I also have a binary log, which I want to partially restore, let's say from position 10 - 2500. How can I do that?
Jake Armstrong's user avatar
3 votes
1 answer
2k views

MySQLDump wrong dump

i'm trying to generate a dump from my database but when i try to load the dump file it gives me an error. What should i do ? My MySQL ( loader) version is mysql "Ver 14.14 Distrib 5.1.57, for apple-...
Guilherme's user avatar
  • 131
3 votes
1 answer
605 views

investigate contents of a sqlite3 file

I have created a new, clean firefox profile from scratch, and added couple of bookmarks. These, I have learned, are stored in places.sqlite file inside the profile directory. This file seems to be too ...
Martin Vegter's user avatar
3 votes
0 answers
936 views

How to do a pgdump with strings encoded?

When I perform a dump with pg_dump tool, with a "flat" sql format, pg_dump create a regular sql file but the restoration with pg_restore fails because the strings are not escaped. For example, single ...
Rémi B.'s user avatar
  • 351
3 votes
2 answers
2k views

How to dump an advanced revelation database? [closed]

Retail store I work with has an ancient inventory/accounting/receiving system that is based off Advanced Revelation. They run the DOS and Windows clients. I'd like to get a dump of their data to ...
Matt Huie's user avatar
2 votes
1 answer
1k views

Why does importing a MySQL dump cause so many I/O?

I imported 100 SQL-format dump files. The total size was 20 GB. The resulting .ibd files after the import had a total size of 40 GB. mysqld.exe wrote 1.1 TB and read 120 GB on the disk. Why so many I/...
Franck Dernoncourt's user avatar
2 votes
1 answer
4k views

Load database from Solaris (Sybase 15.0.3) to VMWare Solaris (Sybase 15.7.0)

I need to perform a load of a Sybase (15.0.3) Database from a Sun Solaris 5.10 environment (on sparc architecture) to Sybase (15.7.0) on Solaris 10 (on i86pc architecture) running on my vmware ...
aF.'s user avatar
  • 255
2 votes
2 answers
3k views

Export pipe-delimited data with progress db

I've been handed a task where we need to dump specific tables in a progress database (10.2B) to pipe-delimited flat files (50+ tables) and send them to a third party for analysis every week. Looking ...
Jeremy Holovacs's user avatar
2 votes
2 answers
1k views

Mysql dump as small bulk insert

In mysql (8.0), there is a table with over 10 million rows, I want to dump it as bulk insert sql. I wish each insert sql contains maybe 1k or 10k rows, so that could execute the sql file quickly, ...
Eric's user avatar
  • 253
2 votes
1 answer
233 views

Large MySQL database dump/restore [closed]

Got a quite interesting task: Have to dump mysql databases from AWS and restore on a private cloud. Databases are quite big: 160GB, 120GB and 21GB each db.sql dump - all 3 in the same aurora rds ...
Gheorghe  Isac's user avatar
2 votes
1 answer
104 views

SQL Server - dump schema into separate files keeping installation order

While dumping SQL Server's schema, I have these two options: single file or multiple files. Single file is good because is can be used to install schema on clean server - parents are installed before ...
Jędrzej Dudkiewicz's user avatar