Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [dump]

137 questions
72
votes
9 answers

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
  • 1,827
  • 5
  • 17
  • 27
55
votes
1 answer

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 "myrole" does not exist So apparently the dump of…
Rob Bednark
  • 2,253
  • 6
  • 22
  • 22
50
votes
1 answer

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
  • 743
  • 2
  • 7
  • 10
40
votes
4 answers

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
  • 503
  • 1
  • 4
  • 7
14
votes
5 answers

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 has gone away everytime I try to import the…
naxoc
  • 241
  • 1
  • 2
  • 5
12
votes
4 answers

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, this is the normal database dump format. Does it…
peterh
  • 2,137
  • 8
  • 28
  • 41
9
votes
1 answer

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

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 2019 Standard 10.0 / Build 17763) on 2 new physical…
SQLpro
  • 550
  • 2
  • 8
8
votes
2 answers

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 resulting dump file doesn't include a DROP PROCEDURE…
nakhli
  • 743
  • 2
  • 7
  • 10
7
votes
1 answer

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 out of space and the dump fails. This is the…
kissgyorgy
  • 345
  • 1
  • 3
  • 8
7
votes
2 answers

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.LOG Did not work These services:
Chris_45
  • 211
  • 1
  • 6
7
votes
3 answers

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 database. However if I don't create a database first,…
Alexandre Sousa
  • 91
  • 1
  • 1
  • 4
6
votes
2 answers

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 way through. But I'm wondering if there's a way to…
dot
  • 781
  • 5
  • 11
  • 22
6
votes
2 answers

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 service to get it going. Here's snippet of the…
Depth of Field
  • 233
  • 1
  • 7
6
votes
1 answer

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: out of shared memory pg_dump: SQL command…
krlmlr
  • 371
  • 1
  • 3
  • 12
6
votes
1 answer

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 could take like an hour in the best cases. I'm also…
Imanol Y.
  • 785
  • 2
  • 10
  • 28
1
2 3
…
9 10