Questions tagged [dump]
The dump tag has no usage guidance.
136
questions
72
votes
9
answers
129k
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
- 1,807
54
votes
1
answer
83k
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 "...
- 2,203
50
votes
1
answer
81k
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?
- 743
36
votes
4
answers
54k
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.
- 463
14
votes
5
answers
48k
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 ...
- 241
11
votes
4
answers
41k
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, ...
- 2,109
9
votes
1
answer
813
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 ...
- 505
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 ...
- 743
7
votes
1
answer
8k
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 ...
- 335
7
votes
2
answers
553
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....
- 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 ...
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 ...
- 1,395
6
votes
2
answers
532
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 ...
- 233
6
votes
1
answer
4k
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 ...
- 61
6
votes
1
answer
9k
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 ...
- 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: ...
- 371
5
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 ...
- 217
5
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 ...
- 723
5
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 ...
- 771
5
votes
1
answer
5k
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=...
- 151
4
votes
4
answers
15k
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
...
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-...
- 143
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 ...
- 141
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 ...
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 ...
- 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 ...
- 191
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 ...
3
votes
4
answers
3k
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> ...
- 171
3
votes
4
answers
42k
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 ...
- 31
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 ...
- 31
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 ...
- 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. ...
- 131
3
votes
1
answer
45
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 ...
- 133
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 ...
- 245
3
votes
2
answers
5k
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 ...
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 ...
- 533
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.
...
- 285
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 ...
- 1,481
3
votes
2
answers
6k
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 ...
- 133
3
votes
1
answer
299
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?
- 135
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-...
- 131
3
votes
1
answer
618
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 ...
3
votes
0
answers
950
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 ...
- 361
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 ...
- 41
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/...
- 2,085
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 ...
- 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 ...
- 1,271
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, ...
- 253
2
votes
1
answer
243
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 ...
2
votes
1
answer
112
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 ...
