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 ...
- 64.7k
29
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)
- 409
14
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 ...
- 316
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 ...
- 64.7k
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....
- 57.3k
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 ...
- 11.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 ...
- 64.7k
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 ...
- 6,479
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 ...
- 9,480
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 ...
- 8,650
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 ...
- 9,396
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 ...
- 41.3k
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 ...
- 39.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 ...
- 47.6k
4
votes
Accepted
Prepend text to mysqldump output at dump creation time
Using echo before and after the mariadb-dump command and then redirecting the output into a file should work at least with bash:
(echo "SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=...
- 421
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 ...
- 8,650
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 ...
- 8,650
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
...
- 31
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 = '...
- 41.1k
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 :...
- 1,515
3
votes
Why is Microsoft SQL Server creating a ton of dump files?
Since these dumps are being created specifically in the polybase subdirectory, you may want to focus specifically on polybase dumps.
At this link, Niels Berglund shows how TCP protocol being disabled ...
- 1,526
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 ...
- 4,463
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 ...
- 233
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
...
- 13.6k
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 ....
- 121
2
votes
Restore/import tables from unsuccessful database dump file
It depends, lets test it.
[oracle@server1 Desktop]$ expdp system/oracle directory=DATA_PUMP_DIR schemas=jay dumpfile=jay.dmp
Export process has started and I tried to shut the database down in a ...
- 8,650
2
votes
MySQL verify backups
This is one of the best posts I've seen on this (regular enough here) topic. Seems to be the quickest way to test a backup.
What you've got to remember is that no process is 100% cast-iron guaranteed ...
- 30.6k
2
votes
Datapump export taking too much time to complete
By having your database is no archive log mode and doing a daily warm backup, you are saying that is acceptable to lose a day's worth of data. If that is the case then fine, you don't need archive log ...
- 1,520
2
votes
Accepted
How do I get the GRANT statements for a specified schema?
Use pg_dump
pg_dump -d myDatabase --schema mySchema --section=pre-data |
grep '^GRANT'
If you want just the GRANT on the schema object.
pg_dump -d myDatabase --schema mySchema --section=pre-data |...
- 64.7k
Only top scored, non community-wiki answers of a minimum length are eligible