39 votes

Export Postgres table as json

If you're using psql then there is no reason to use \COPY at all. \t \a \o file.json SELECT row_to_json(r) FROM my_table AS r; This is the same method we use to get png/jpgs/tifs out of the database ...
Evan Carroll's user avatar
  • 63.1k
28 votes

Export Postgres table as json

Please find below the only answer that outputs actually valid JSON (i.e. array of objects). \t \a \o data.json select json_agg(t) FROM (SELECT * from table) t; (source)
Gunar Gessner's user avatar
15 votes
Accepted

how to track progress of a large postgres dump

You can see a rough progress using the TOC list. First, get the TOC list of objects to be restored: pg_restore -l -f list.toc db.dump Then, you can see the TOC list line by line and compare the ...
MatheusOl's user avatar
  • 2,519
12 votes

how to track progress of a large postgres dump

Valid for Unix/Linux environments: The Pipe Viewer (pv) utility can be used trace the backup progress. The pv animates your shell with details about the elapsed time and transferred bytes. Below is ...
R0B0T-B0BA's user avatar
10 votes

Export Postgres table as json

I will add a special caveat to Verace's answer. You need to do post processing on the outputted JSON file if you have text columns with backslash characters: \. Otherwise you will get duplicate (\ ->...
Adam Gent's user avatar
  • 209
8 votes

Dump Oracle database into a textual SQL script

Does it exist on Oracle? Similar utility exists in Oracle environment called Data Pump. How can it be done? Use expdp command line tool to perform full database export. Example: expdp hr ...
atokpas's user avatar
  • 8,634
8 votes

How do I disable foreign key constraints in my Postgres dump file?

I'm not sure what's happening, but you're certainly not giving us enough information. ALTER TABLE DISABLE TRIGGER ALL works fine on disabling FOREIGN KEYS. test=# CREATE TABLE table1 ( id serial ...
Evan Carroll's user avatar
  • 63.1k
6 votes

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

Those "frozen" messages refer to pages that are known from the visibility map's freeze bits to contain only frozen tuples. A frozen tuple is one that all current and future transactions can "see", i.e....
Craig Ringer's user avatar
  • 56.4k
6 votes

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

In SQL Server Management Studio (SSMS), you can generate a script that would create the structural components of your database (tables and views, as well as users, functions, and procedures). You ...
RDFozz's user avatar
  • 11.6k
5 votes
Accepted

Dump Oracle database into a textual SQL script

There is no such thing in the database itself. You can perform something similar in SQL Developer, with the Database Export tool. How to Export Data using SQL Developer With the Tools / Database ...
Balazs Papp's user avatar
  • 40.7k
5 votes
Accepted

Parsing COPY's binary format to access a tsrange

As a minor note, COPY .. (WITH BINARY) doesn't have brackets. It's the flags (which represent amongst other things the brackets). COPY ... (WITH BINARY) From the docs on COPY To determine the ...
Evan Carroll's user avatar
  • 63.1k
5 votes

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

You can use the --one-database option or -o for short: mysql ... -o mysb3 < /path/to/dump.sql Note that the documentation page in the link above states: This option is rudimentary and should be ...
dbdemon's user avatar
  • 6,351
5 votes

Should databases be dumped to SQL format or custom archive?

Ask yourself the same question that you ask whenever you need to store any Data: How are you going to use this Data Backup? If you need to get "into" the file and manipulate it (as a ...
Phill  W.'s user avatar
  • 8,726
4 votes
Accepted

Sequence number shows a lower value after dump file import

If you run export command against live database without parameters like flashback_scn or flashback_time which ensures consistency during the export, then this can be an expected behavior because these ...
atokpas's user avatar
  • 8,634
4 votes
Accepted

Lock database in time

You can use set timestamp <unix_epoch> which will make now() be fixed -- always return that value. See https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_timestamp Note ...
Colin 't Hart's user avatar
4 votes
Accepted

Fastest way to extract full table in Postgres

Your current idea of using a range query on the primary key is probably the best bet. I don't see how partial indexes could help with that. You would need a series of partial indexes which would in ...
jjanes's user avatar
  • 39.8k
4 votes

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

Four quick things that come to mind are: Relationships between entities (and the representation of a complex object) are more easily defined within the same XML file, whereas a CSV data dump usually ...
J.D.'s user avatar
  • 37.5k
4 votes

SQL Server generating sporadic Memory Dumps

OPENQUERY & OPENROWSET functions are used quite frequently, using the latest ACE & ODBC drivers. Sorry. That's not supported, and now you know why. The Office System Drivers are only ...
David Browne - Microsoft's user avatar
3 votes

Opening a .backup file for PostgreSQL

It could be most useful to start by using the pg_restore command to check the format of the file that's been sent. If your file was named "mydata.backup", the command would be: pg_restore --list ...
Warwick's user avatar
  • 31
3 votes

Difference in database dump file size

Data pump uses BLOCKS method to estimate the size of the dump by default during the export. The estimate is calculated by multiplying the number of database blocks used by the source objects, multiply ...
atokpas's user avatar
  • 8,634
3 votes
Accepted

Encountering "Snapshot too old" error during execution of expdp command

The cause of the ORA-01555 error is undo_retention parameter has the lower value as compared to the time taken to complete the export with the parameter consistent=y(which is deprecated in 11g and ...
atokpas's user avatar
  • 8,634
3 votes

How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0

Are you sure about the target and source version? I did here and worked right. My source DB version was 12.1.0.2.0 and the target was 12.2.0.1.0. Please, see my example below: $ cat myfile.par ...
D. Côrtes's user avatar
3 votes

Tablespace 'XXX_SML_INDEX' does not exist using impdb

Find the default tablespace at database level (which is not necessarily the default tablespace of your user): SQL> select property_value from database_properties where property_name = '...
Balazs Papp's user avatar
  • 40.7k
3 votes

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

I am not sure what you tried so far, please post error message for better understanding, Try below and post a comment if this works for you or not. you can use pg_restore to restore a single table :...
CR241's user avatar
  • 1,495
3 votes

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

Try this: sed -n '/^-- Current Database: `mydb1`/,/^-- Current Database: `/p' dump.sql > mydb1.sql Other ways of using sed to get specific tables: sed -n -e '/CREATE TABLE.*`table_name`/,/CREATE ...
Ergest Basha's user avatar
  • 3,935
3 votes
Accepted

SQL Server generating sporadic Memory Dumps

Just a follow-up on this - We had our Memory dump files analyzed by Microsoft, and it turns out the OPENQUERY and OPENROWSET SQL statements have been the culprit. We updated the Microsoft Access ...
Depth of Field's user avatar
2 votes
Accepted

How do I export Postgres data in "Insert ... on conflict" format instead of "cp" format?

pg_dump won't allow you to do exactly what you ask for, but you have an option that might be good enough. According to the documentation of pg_dump, you have the --inserts option: --inserts ...
joanolo's user avatar
  • 13.4k
2 votes

how to track progress of a large postgres dump

I didn't find a accurate and efficient way, yet. A compromise way I have to took a compromise way: ① query row count ② check dump.sql row line count Statistics row only //exclude other line in ....
yurenchen's user avatar
  • 121
2 votes

mysql restore specific date data from a dump file

You have two alternatives: 1. Load the file into a new database table and do a query for the particular date that you want. or 2. Hand edit the dump file and load that (also in a new database). ...
Vérace's user avatar
  • 29.9k

Only top scored, non community-wiki answers of a minimum length are eligible